/*-----------------------------------------Tables-----------------------------------------*/

/*[ACCOUNTSECTIONS]*/
/*[ACCOUNTS]*/
/*[USERS]*/
/*[PWDPROTECTION]*/

/*[DEPARTMENTS]*/

/*[CONTACTGROUPS]*/
/*[CONTACTS]*/

/*[GROUPS]*/
/*[GROUPMEMBERS]*/
/*[GROUPSHAREFILES]*/
/*[OFFGROUPMSGS]*/
/*[OFFGROUPSUPEROBJS]*/
/*[JOINGROUPREQUESTS]*/
/*[JOINGROUPRESPONSES]*/

/*[ROOMS]*/
/*[ROOMMEMBERS]*/
/*[ROOMSHAREFILES]*/
/*[OFFROOMMSGS]*/
/*[OFFROOMSUPEROBJS]*/
/*[JOINROOMREQUESTS]*/
/*[JOINROOMRESPONSES]*/

/*[WEBVISITORTOTAL]*/
/*[WEBVISITORS]*/
/*[WEBVISITORMESSAGES]*/

/*[WEBAPPS]*/

/*[RECENTITEMS]*/

/*[SYSMSGS]*/

/*[SENDSMS]*/

/*[ADMINS]*/
/*[ADMINLOGS]*/

/*[OFFLINEFILES]*/
/*[OFFLINEMESSAGES]*/
/*[OFFLINESUPEROBJECTS]*/

/*[JOINREQUESTS]*/
/*[JOINRESPONSES]*/

/*-----------------------------------------Tables-----------------------------------------*/

/*[ACCOUNTSECTIONS]*/

CREATE TABLE ACCOUNTSECTIONS (
	BEGINUSERID		INT NOT NULL,
	ENDUSERID		INT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ACCOUNTSECTIONS ADD CONSTRAINT PK_ACCOUNTSECTIONS PRIMARY KEY CLUSTERED (BEGINUSERID, ENDUSERID);
GO

CREATE INDEX ACCOUNTSECTIONS_IDX_CREATETIME ON ACCOUNTSECTIONS (CREATETIME);
GO

/*[ACCOUNTS]*/

CREATE TABLE ACCOUNTS (
	USERID			INT NOT NULL,
	RANDOMVALUE		FLOAT NOT NULL,
	REGISTED		SMALLINT NOT NULL,
	REGISTERTIME		DATETIME,
	REGISTERIPADDR		NVARCHAR(50) NOT NULL,
	SECTION			NVARCHAR(50) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY CLUSTERED (USERID);
GO

CREATE INDEX ACCOUNTS_IDX_RANDOMVALUE ON ACCOUNTS (RANDOMVALUE);
GO

CREATE INDEX ACCOUNTS_IDX_REGISTED ON ACCOUNTS (REGISTED);
GO

CREATE INDEX ACCOUNTS_IDX_CREATETIME ON ACCOUNTS (CREATETIME);
GO

/*[USERS]*/

CREATE TABLE USERS (
	USERID			INT NOT NULL,
	USERNAME		NVARCHAR(50) NOT NULL,
	USERTYPE		SMALLINT NOT NULL,
	CANFOUND		SMALLINT,

	NICKNAME		NVARCHAR(20) NOT NULL,
	SIGNATURE		NVARCHAR(255) NOT NULL,
	ACTUALNAME		NVARCHAR(20) NOT NULL,
	SEX			SMALLINT NOT NULL,
	AGE			SMALLINT NOT NULL,
	BIRTH_YEAR		SMALLINT NOT NULL,
	BIRTH_MONTH		SMALLINT NOT NULL,
	BIRTH_DAY		SMALLINT NOT NULL,
	COUNTRY			SMALLINT NOT NULL,
	PROVINCE		SMALLINT NOT NULL,
	CITY			SMALLINT NOT NULL,
	AREA			SMALLINT NOT NULL,
	ADDRESS            NVARCHAR(50) NOT NULL,
	TELEPHONE          NVARCHAR(20) NOT NULL,
	TELEPHONE_SHORT    NVARCHAR(20) NOT NULL,
	TELEPHONE2         NVARCHAR(20) NOT NULL,
	TELEPHONE2_SHORT   NVARCHAR(20) NOT NULL,
	MOBILE             NVARCHAR(20) NOT NULL,
	MOBILE_SHORT       NVARCHAR(20) NOT NULL,
	MOBILE2            NVARCHAR(20) NOT NULL,
	MOBILE2_SHORT      NVARCHAR(20) NOT NULL,
	FAX                NVARCHAR(20) NOT NULL,
	QQ                 NVARCHAR(50) NOT NULL,
	WEIXIN             NVARCHAR(50) NOT NULL,
	WEIBO              NVARCHAR(50) NOT NULL,
	EMAIL              NVARCHAR(50) NOT NULL,
	ROOMNAME           NVARCHAR(50) NOT NULL,
	ROOMNUMBER         NVARCHAR(50) NOT NULL,
	IDNUMBER           NVARCHAR(50) NOT NULL,
	CARNUMBER          NVARCHAR(50) NOT NULL,
	HOMEPAGE           NVARCHAR(80) NOT NULL,
	DEPARTID           INT NOT NULL,
	DEPARTNAME         NVARCHAR(50) NOT NULL,
	JOBTITLE           NVARCHAR(50) NOT NULL,
	JOBNUMBER          NVARCHAR(20) NOT NULL,
	INTRODUCTION       NVARCHAR(255) NOT NULL,

	FACEFILE		NVARCHAR(255) NOT NULL,
	PHOTOFILE		NVARCHAR(255) NOT NULL,

	LOGINSTATUS		SMALLINT NOT NULL,
	LOGINTIMES		BIGINT NOT NULL,
	LASTLOGINTIME		DATETIME,
	CLIENTIPADDR		NVARCHAR(50) NOT NULL,
	CLIENTLOCATION		NVARCHAR(50) NOT NULL,
	LASTCLIENTIPADDR	NVARCHAR(50) NOT NULL,
	LASTCLIENTLOCATION	NVARCHAR(50) NOT NULL,

	PWD			NVARCHAR(255) NOT NULL,
	SALT			NVARCHAR(6) NOT NULL,
	TOKEN			NVARCHAR(255) NOT NULL,
	TOKENUPDATETIME		DATETIME,
	LOCKED		SMALLINT NOT NULL,

	ONLYFINDMEBYID		SMALLINT NOT NULL,
	JOINSETTING		SMALLINT NOT NULL,
	JOINQUESTION		NVARCHAR(50) NOT NULL,
	JOINANSWER		NVARCHAR(50) NOT NULL,

	LASTRECVSYSMSGS		DATETIME,
        LINKDEPARTID            INT NOT NULL,
	USERPOSITION            INT NOT NULL,

        SMSTOTAL                INT NOT NULL,
        SMSDAYLIMIT             INT NOT NULL,
        SMSDAYSTART             DATETIME,
        SMSDAYSENDED            INT NOT NULL,
        SMSTOTALSENDED          INT NOT NULL,  

	MODIFYTIME		DATETIME NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE USERS ADD CONSTRAINT PK_USERS PRIMARY KEY CLUSTERED (USERID);
GO

CREATE UNIQUE INDEX USERS_IDX_USERNAME ON USERS (USERNAME);
GO

CREATE INDEX USERS_IDX_USERTYPE ON USERS (USERTYPE);
GO

CREATE INDEX USERS_IDX_CANFOUND ON USERS (CANFOUND);
GO

CREATE INDEX USERS_IDX_NICKNAME ON USERS (NICKNAME);
GO

CREATE INDEX USERS_IDX_ACTUALNAME ON USERS (ACTUALNAME);
GO

CREATE INDEX USERS_IDX_SEX ON USERS (SEX);
GO

CREATE INDEX USERS_IDX_AGE ON USERS (AGE);
GO

CREATE INDEX USERS_IDX_COUNTRY ON USERS (COUNTRY);
GO

CREATE INDEX USERS_IDX_PROVINCE ON USERS (PROVINCE);
GO

CREATE INDEX USERS_IDX_CITY ON USERS (CITY);
GO

CREATE INDEX USERS_IDX_AREA ON USERS (AREA);
GO

CREATE INDEX USERS_IDX_DEPARTID ON USERS (DEPARTID);
GO

CREATE INDEX USERS_IDX_LOGINSTATUS ON USERS (LOGINSTATUS);
GO

CREATE INDEX USERS_IDX_PWD ON USERS (PWD);
GO

CREATE INDEX USERS_IDX_SALT ON USERS (SALT);
GO

CREATE INDEX USERS_IDX_TOKEN ON USERS (TOKEN);
GO

CREATE INDEX USERS_IDX_LOCKED ON USERS (LOCKED);
GO

CREATE INDEX USERS_IDX_ONLYFINDMEBYID ON USERS (ONLYFINDMEBYID);
GO

CREATE INDEX USERS_IDX_CREATETIME ON USERS (CREATETIME);
GO

/*[PWDPROTECTION]*/

CREATE TABLE PWDPROTECTION (
	USERID			INT NOT NULL,
	QUESTION1		NVARCHAR(50) NOT NULL,
	ANSWER1			NVARCHAR(50) NOT NULL,
	QUESTION2		NVARCHAR(50) NOT NULL,
	ANSWER2			NVARCHAR(50) NOT NULL,
	QUESTION3		NVARCHAR(50) NOT NULL,
	ANSWER3			NVARCHAR(50) NOT NULL,
	LASTMODIFYTIME		DATETIME NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE PWDPROTECTION ADD CONSTRAINT PK_PWDPROTECTION PRIMARY KEY CLUSTERED (USERID);
GO

CREATE INDEX PWDPROTECTION_IDX_CREATETIME ON PWDPROTECTION (CREATETIME);
GO

/*[DEPARTMENTS]*/

CREATE TABLE DEPARTMENTS (
	DEPARTID		INT NOT NULL,
	DEPARTNAME		NVARCHAR(50) NOT NULL,
	ORDERVALUE		INT NOT NULL,
	PARENTDEPARTID		INT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE DEPARTMENTS ADD CONSTRAINT PK_DEPARTMENTS PRIMARY KEY CLUSTERED (DEPARTID);
GO

CREATE INDEX DEPARTMENTS_IDX_ORDERVALUE ON DEPARTMENTS (ORDERVALUE);
GO

CREATE INDEX DEPARTMENTS_IDX_PARENTDEPARTID ON DEPARTMENTS (PARENTDEPARTID);
GO

CREATE INDEX DEPARTMENTS_IDX_CREATETIME ON DEPARTMENTS (CREATETIME);
GO

/*[CONTACTGROUPS]*/

CREATE TABLE CONTACTGROUPS (
	CONTACTGROUPID		INT NOT NULL,
	USERID			INT NOT NULL,
	CONTACTGROUPNAME	NVARCHAR(20) NOT NULL,
	HIDEWHENONLINE		SMALLINT NOT NULL,
	ONLINEWHENHIDE		SMALLINT NOT NULL,
	REMINDMEWHENLOGIN	SMALLINT NOT NULL,
	NOMESSAGE		SMALLINT NOT NULL,
	ORDERVALUE		INT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE CONTACTGROUPS ADD CONSTRAINT PK_CONTACTGROUPS PRIMARY KEY CLUSTERED (CONTACTGROUPID, USERID);
GO

CREATE INDEX CONTACTGROUPS_IDX_ORDERVALUE ON CONTACTGROUPS (ORDERVALUE);
GO

CREATE INDEX CONTACTGROUPS_IDX_CREATETIME ON CONTACTGROUPS (CREATETIME);
GO

/*[CONTACTS]*/

CREATE TABLE CONTACTS (
	CONTACTID		INT NOT NULL,
	USERID			INT NOT NULL,
	CONTACTGROUPID		INT NOT NULL,
	REMARKNAME		NVARCHAR(20) NOT NULL,
	HIDEWHENONLINE		SMALLINT NOT NULL,
	ONLINEWHENHIDE		SMALLINT NOT NULL,
	REMINDMEWHENLOGIN	SMALLINT NOT NULL,
	NOMESSAGE		SMALLINT NOT NULL,
	JOINTIME		DATETIME NOT NULL
);
GO

ALTER TABLE CONTACTS ADD CONSTRAINT PK_CONTACTS PRIMARY KEY CLUSTERED (CONTACTID,USERID);
GO

CREATE INDEX CONTACTS_IDX_CONTACTGROUPID ON CONTACTS (CONTACTGROUPID);
GO

CREATE INDEX CONTACTS_IDX_JOINTIME ON CONTACTS (JOINTIME);
GO

/*[ADDRLSTCATEGORYS]*/

CREATE TABLE ADDRLSTCATEGORYS (
    CATEGORYID         INT NOT NULL,
    USERID             INT NOT NULL,    
    CATEGORYNAME       NVARCHAR(50) NOT NULL,
    ORDERVALUE         INT NOT NULL,
    PARENTCATEGORYID   INT NOT NULL,
    CREATETIME         DATETIME NOT NULL
);
GO

ALTER TABLE ADDRLSTCATEGORYS ADD CONSTRAINT PK_ADDRLSTCATEGORYS PRIMARY KEY CLUSTERED (CATEGORYID);
GO

CREATE INDEX ADDRLSTCATEGORYS_IDX_USERID ON ADDRLSTCATEGORYS (USERID);
GO

CREATE INDEX ADDRLSTCATEGORYS_IDX_ORDERVALUE ON ADDRLSTCATEGORYS (ORDERVALUE);
GO

CREATE INDEX ADDRLSTCATEGORYS_IDX_P_C_ID ON ADDRLSTCATEGORYS (PARENTCATEGORYID);
GO

CREATE INDEX ADDRLSTCATEGORYS_IDX_CREATETIME ON ADDRLSTCATEGORYS (CREATETIME);
GO

/*[ADDRLSTITEMS]*/

CREATE TABLE ADDRLSTITEMS (
    ITEMID             INT NOT NULL,
    CATEGORYID         INT NOT NULL,
    USERID             INT NOT NULL,    
    ACTUALNAME         NVARCHAR(20) NOT NULL,
    SEX                SMALLINT NOT NULL,
    AGE                SMALLINT NOT NULL,
    ZIPCODE            NVARCHAR(10) NOT NULL,
    ADDRESS            NVARCHAR(50) NOT NULL,
    TELEPHONE          NVARCHAR(20) NOT NULL,
    TELEPHONE_SHORT    NVARCHAR(20) NOT NULL,
    TELEPHONE2         NVARCHAR(20) NOT NULL,
    TELEPHONE2_SHORT   NVARCHAR(20) NOT NULL,
    MOBILE             NVARCHAR(20) NOT NULL,
    MOBILE_SHORT       NVARCHAR(20) NOT NULL,
    MOBILE2            NVARCHAR(20) NOT NULL,
    MOBILE2_SHORT      NVARCHAR(20) NOT NULL,
    FAX                NVARCHAR(20) NOT NULL,
    QQ                 NVARCHAR(50) NOT NULL,
    WEIXIN             NVARCHAR(50) NOT NULL,
    WEIBO              NVARCHAR(50) NOT NULL,
    EMAIL              NVARCHAR(50) NOT NULL,
    ROOMNAME           NVARCHAR(50) NOT NULL,
    ROOMNUMBER         NVARCHAR(50) NOT NULL,
    IDNUMBER           NVARCHAR(50) NOT NULL,
    CARNUMBER          NVARCHAR(50) NOT NULL,
    DESCRIPTION        NVARCHAR(255) NOT NULL,
    CREATETIME         DATETIME NOT NULL
);
GO

ALTER TABLE ADDRLSTITEMS ADD CONSTRAINT PK_ADDRLSTITEMS PRIMARY KEY CLUSTERED (ITEMID);
GO

CREATE INDEX ADDRLSTITEMS_IDX_CATEGORYID ON ADDRLSTITEMS (CATEGORYID);
GO

CREATE INDEX ADDRLSTITEMS_IDX_USERID ON ADDRLSTITEMS (USERID);
GO

CREATE INDEX ADDRLSTITEMS_IDX_CREATETIME ON ADDRLSTITEMS (CREATETIME);
GO

/*[GROUPS]*/

CREATE TABLE GROUPS (
	GROUPID			INT NOT NULL,
	GROUPNAME		NVARCHAR(20) NOT NULL,
	INTRODUCTION		NVARCHAR(255) NOT NULL,
	NOTICE			NVARCHAR(255) NOT NULL,
	JOINSETTING		SMALLINT NOT NULL,
	CREATORID		INT NOT NULL,
	OWNERID			INT NOT NULL,
	OWNERUSERTYPE		SMALLINT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY CLUSTERED (GROUPID);
GO

CREATE INDEX GROUPS_IDX_GROUPNAME ON GROUPS (GROUPNAME);
GO

CREATE INDEX GROUPS_IDX_CREATORID ON GROUPS (CREATORID);
GO

CREATE INDEX GROUPS_IDX_OWNERID ON GROUPS (OWNERID);
GO

CREATE INDEX GROUPS_IDX_OWNERUSERTYPE ON GROUPS (OWNERUSERTYPE);
GO

CREATE INDEX GROUPS_IDX_CREATETIME ON GROUPS (CREATETIME);
GO

/*[GROUPMEMBERS]*/

CREATE TABLE GROUPMEMBERS (
	MEMBERID		INT NOT NULL,
	GROUPID			INT NOT NULL,
	ISMANAGER		SMALLINT NOT NULL,
	LASTRECVMSGSID		BIGINT NOT NULL,
	MSGHINTSETTING		SMALLINT NOT NULL,
	MODIFYCARDBYMNG		SMALLINT NOT NULL,
	CARD_NAME		NVARCHAR(20) NOT NULL,
	CARD_SEX		SMALLINT NOT NULL,
	CARD_NUMBER		NVARCHAR(20) NOT NULL,
	CARD_EMAIL		NVARCHAR(50) NOT NULL,
	CARD_REMARK		NVARCHAR(255) NOT NULL,	
	JOINTIME		DATETIME NOT NULL
);
GO

ALTER TABLE GROUPMEMBERS ADD CONSTRAINT PK_GROUPMEMBERS PRIMARY KEY CLUSTERED (MEMBERID,GROUPID);
GO

CREATE INDEX GROUPMEMBERS_IDX_ISMANAGER ON GROUPMEMBERS (ISMANAGER);
GO

CREATE INDEX GROUPMEMBERS_IDX_LASTRECVMSGSID ON GROUPMEMBERS (LASTRECVMSGSID);
GO

CREATE INDEX GROUPMEMBERS_IDX_JOINTIME ON GROUPMEMBERS (JOINTIME);
GO

/*[GROUPSHAREFILES]*/

CREATE TABLE GROUPSHAREFILES (
	SID			VARCHAR(50) NOT NULL,
	MEMBERID		INT NOT NULL,
	GROUPID			INT NOT NULL,
	FILENAMEORDIR 		NVARCHAR(255) NOT NULL,
	ISDIR			SMALLINT NOT NULL,
        ALLFILECOUNT            INT NOT NULL,
	ALLFILESIZE             BIGINT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE GROUPSHAREFILES ADD CONSTRAINT PK_GROUPSHAREFILES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX GROUPSHAREFILES_IDX_MEMBERID ON GROUPSHAREFILES (MEMBERID);
GO

CREATE INDEX GROUPSHAREFILES_IDX_GROUPID ON GROUPSHAREFILES (GROUPID);
GO

CREATE INDEX GROUPSHAREFILES_IDX_CREATETIME ON GROUPSHAREFILES (CREATETIME);
GO

/*[OFFGROUPMSGS]*/

CREATE TABLE OFFGROUPMSGS (
    SID				BIGINT IDENTITY(1,1) NOT NULL,
    GROUPID			INT NOT NULL,
    MSGID			VARCHAR(50) NOT NULL,
    MSGLEVEL			SMALLINT NOT NULL,
    [CONTENT]			TEXT NOT NULL,
    FONT			NVARCHAR(50) NOT NULL,
    SENDER_USERID		INT NOT NULL,
    SENDER_NICKNAME		NVARCHAR(20) NOT NULL,
    SENDER_ACTUALNAME		NVARCHAR(20) NOT NULL,
    SENDTIME			DATETIME NOT NULL
);
GO

ALTER TABLE OFFGROUPMSGS ADD CONSTRAINT PK_OFFGROUPMSGS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFGROUPMSGS_IDX_GROUPID ON OFFGROUPMSGS (GROUPID);
GO

CREATE INDEX OFFGROUPMSGS_IDX_MSGID ON OFFGROUPMSGS (MSGID);
GO

CREATE INDEX OFFGROUPMSGS_IDX_SENDER_USERID ON OFFGROUPMSGS (SENDER_USERID);
GO

CREATE INDEX OFFGROUPMSGS_IDX_S_NICKNAME ON OFFGROUPMSGS (SENDER_NICKNAME);
GO

CREATE INDEX OFFGROUPMSGS_IDX_SENDTIME ON OFFGROUPMSGS (SENDTIME);
GO

/*[OFFGROUPSUPEROBJS]*/

CREATE TABLE OFFGROUPSUPEROBJS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,	
	GROUPID			INT NOT NULL,
	MSGID			VARCHAR(50) NOT NULL,
	FROMUSERID		INT NOT NULL,
	SUPEROBJCODE		NVARCHAR(1024) NOT NULL,
	LOCALFILENAME		NVARCHAR(1024) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE OFFGROUPSUPEROBJS ADD CONSTRAINT PK_OFFGROUPSUPEROBJS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFGROUPSUPEROBJS_IDX_GROUPID ON OFFGROUPSUPEROBJS (GROUPID);
GO

CREATE INDEX OFFGROUPSUPEROBJS_IDX_MSGID ON OFFGROUPSUPEROBJS (MSGID);
GO

CREATE INDEX OFFGROUPSUPEROBJS_IDX_CTIME ON OFFGROUPSUPEROBJS (CREATETIME);
GO

/*[JOINGROUPREQUESTS]*/

CREATE TABLE JOINGROUPREQUESTS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	GROUPID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINGROUPREQUESTS ADD CONSTRAINT PK_JOINGROUPREQUESTS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINGROUPREQUESTS_IDX_USERID ON JOINGROUPREQUESTS (USERID);
GO

CREATE INDEX JOINGROUPREQUESTS_IDX_GROUPID ON JOINGROUPREQUESTS (GROUPID);
GO

CREATE INDEX JOINGROUPREQUESTS_IDX_SENDER ON JOINGROUPREQUESTS (SENDER);
GO

CREATE INDEX JOINGROUPREQUESTS_IDX_SENDTIME ON JOINGROUPREQUESTS (SENDTIME);
GO

/*[JOINGROUPRESPONSES]*/

CREATE TABLE JOINGROUPRESPONSES (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	GROUPID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	AGREE			SMALLINT NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINGROUPRESPONSES ADD CONSTRAINT PK_JOINGROUPRESPONSES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINGROUPRESPONSES_IDX_USERID ON JOINGROUPRESPONSES (USERID);
GO

CREATE INDEX JOINGROUPRESPONSES_IDX_GROUPID ON JOINGROUPRESPONSES (GROUPID);
GO

CREATE INDEX JOINGROUPRESPONSES_IDX_SENDER ON JOINGROUPRESPONSES (SENDER);
GO

CREATE INDEX JOINGROUPRESPONSES_IDX_SENDTIME ON JOINGROUPRESPONSES (SENDTIME);
GO

/*[ROOMS]*/

CREATE TABLE ROOMS (
	ROOMID			INT NOT NULL,
	ROOMNAME		NVARCHAR(20) NOT NULL,
	INTRODUCTION		NVARCHAR(255) NOT NULL,
	NOTICE			NVARCHAR(255) NOT NULL,
	JOINSETTING		SMALLINT NOT NULL,
	CREATORID		INT NOT NULL,
	OWNERID			INT NOT NULL,
	OWNERUSERTYPE		SMALLINT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ROOMS ADD CONSTRAINT PK_ROOMS PRIMARY KEY CLUSTERED (ROOMID);
GO

CREATE INDEX ROOMS_IDX_CREATORID ON ROOMS (CREATORID);
GO

CREATE INDEX ROOMS_IDX_OWNERID ON ROOMS (OWNERID);
GO

CREATE INDEX ROOMS_IDX_OWNERUSERTYPE ON ROOMS (OWNERUSERTYPE);
GO

CREATE INDEX ROOMS_IDX_CREATETIME ON ROOMS (CREATETIME);
GO

/*[ROOMMEMBERS]*/

CREATE TABLE ROOMMEMBERS (
	MEMBERID		INT NOT NULL,
	ROOMID			INT NOT NULL,
	ISMANAGER		SMALLINT NOT NULL,
	LASTRECVMSGSID		BIGINT NOT NULL,
	MSGHINTSETTING		SMALLINT NOT NULL,
	MODIFYCARDBYMNG		SMALLINT NOT NULL,
	CARD_NAME		NVARCHAR(20) NOT NULL,
	CARD_SEX		SMALLINT NOT NULL,
	CARD_NUMBER		NVARCHAR(20) NOT NULL,
	CARD_EMAIL		NVARCHAR(50) NOT NULL,
	CARD_REMARK		NVARCHAR(255) NOT NULL,	
	JOINTIME		DATETIME NOT NULL
);
GO

ALTER TABLE ROOMMEMBERS ADD CONSTRAINT PK_ROOMMEMBERS PRIMARY KEY CLUSTERED (MEMBERID,ROOMID);
GO

CREATE INDEX ROOMMEMBERS_IDX_ISMANAGER ON ROOMMEMBERS (ISMANAGER);
GO

CREATE INDEX ROOMMEMBERS_IDX_LASTRECVMSGSID ON ROOMMEMBERS (LASTRECVMSGSID);
GO

CREATE INDEX ROOMMEMBERS_IDX_JOINTIME ON ROOMMEMBERS (JOINTIME);
GO

/*[ROOMSHAREFILES]*/

CREATE TABLE ROOMSHAREFILES (
	SID			VARCHAR(50) NOT NULL,
	MEMBERID		INT NOT NULL,
	ROOMID			INT NOT NULL,
	FILENAMEORDIR 		NVARCHAR(255) NOT NULL,
	ISDIR			SMALLINT NOT NULL,
        ALLFILECOUNT            INT NOT NULL,
	ALLFILESIZE             BIGINT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ROOMSHAREFILES ADD CONSTRAINT PK_ROOMSHAREFILES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX ROOMSHAREFILES_IDX_MEMBERID ON ROOMSHAREFILES (MEMBERID);
GO

CREATE INDEX ROOMSHAREFILES_IDX_ROOMID ON ROOMSHAREFILES (ROOMID);
GO

CREATE INDEX ROOMSHAREFILES_IDX_CREATETIME ON ROOMSHAREFILES (CREATETIME);
GO

/*[OFFROOMMSGS]*/

CREATE TABLE OFFROOMMSGS (
    SID				BIGINT IDENTITY(1,1) NOT NULL,
    ROOMID			INT NOT NULL,
    MSGID			VARCHAR(50) NOT NULL,
    MSGLEVEL			SMALLINT NOT NULL,
    [CONTENT]			TEXT NOT NULL,
    FONT			NVARCHAR(50) NOT NULL,
    SENDER_USERID		INT NOT NULL,
    SENDER_NICKNAME		NVARCHAR(20) NOT NULL,
    SENDER_ACTUALNAME		NVARCHAR(20) NOT NULL,
    SENDTIME			DATETIME NOT NULL
);
GO

ALTER TABLE OFFROOMMSGS ADD CONSTRAINT PK_OFFROOMMSGS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFROOMMSGS_IDX_ROOMID ON OFFROOMMSGS (ROOMID);
GO

CREATE INDEX OFFROOMMSGS_IDX_MSGID ON OFFROOMMSGS (MSGID);
GO

CREATE INDEX OFFROOMMSGS_IDX_SENDER_USERID ON OFFROOMMSGS (SENDER_USERID);
GO

CREATE INDEX OFFROOMMSGS_IDX_SENDER_NICKNAME ON OFFROOMMSGS (SENDER_NICKNAME);
GO

CREATE INDEX OFFROOMMSGS_IDX_SENDTIME ON OFFROOMMSGS (SENDTIME);
GO

/*[OFFROOMSUPEROBJS]*/

CREATE TABLE OFFROOMSUPEROBJS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,	
	ROOMID			INT NOT NULL,
	MSGID			VARCHAR(50) NOT NULL,
	FROMUSERID		INT NOT NULL,
	SUPEROBJCODE		NVARCHAR(1024) NOT NULL,
	LOCALFILENAME		NVARCHAR(1024) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE OFFROOMSUPEROBJS ADD CONSTRAINT PK_OFFROOMSUPEROBJS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFROOMSUPEROBJS_IDX_ROOMID ON OFFROOMSUPEROBJS (ROOMID);
GO

CREATE INDEX OFFROOMSUPEROBJS_IDX_MSGID ON OFFROOMSUPEROBJS (MSGID);
GO

CREATE INDEX OFFROOMSUPEROBJS_IDX_CREATETIME ON OFFROOMSUPEROBJS (CREATETIME);
GO

/*[JOINROOMREQUESTS]*/

CREATE TABLE JOINROOMREQUESTS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	ROOMID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINROOMREQUESTS ADD CONSTRAINT PK_JOINROOMREQUESTS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINROOMREQUESTS_IDX_USERID ON JOINROOMREQUESTS (USERID);
GO

CREATE INDEX JOINROOMREQUESTS_IDX_ROOMID ON JOINROOMREQUESTS (ROOMID);
GO

CREATE INDEX JOINROOMREQUESTS_IDX_SENDER ON JOINROOMREQUESTS (SENDER);
GO

CREATE INDEX JOINROOMREQUESTS_IDX_SENDTIME ON JOINROOMREQUESTS (SENDTIME);
GO

/*[JOINROOMRESPONSES]*/

CREATE TABLE JOINROOMRESPONSES (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	ROOMID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	AGREE			SMALLINT NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINROOMRESPONSES ADD CONSTRAINT PK_JOINROOMRESPONSES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINROOMRESPONSES_IDX_USERID ON JOINROOMRESPONSES (USERID);
GO

CREATE INDEX JOINROOMRESPONSES_IDX_ROOMID ON JOINROOMRESPONSES (ROOMID);
GO

CREATE INDEX JOINROOMRESPONSES_IDX_SENDER ON JOINROOMRESPONSES (SENDER);
GO

CREATE INDEX JOINROOMRESPONSES_IDX_SENDTIME ON JOINROOMRESPONSES (SENDTIME);
GO

/*[WEBVISITORTOTAL]*/

CREATE TABLE WEBVISITORTOTAL (
	KEYNAME			NVARCHAR(50) NOT NULL,
	KEYVALUE		INT NOT NULL
);
GO

ALTER TABLE WEBVISITORTOTAL ADD CONSTRAINT PK_WEBVISITORTOTAL PRIMARY KEY CLUSTERED (KEYNAME);
GO

CREATE INDEX WEBVISITORTOTAL_IDX_KEYVALUE ON WEBVISITORTOTAL (KEYVALUE);
GO

insert into WEBVISITORTOTAL(KEYNAME, KEYVALUE) values('VISITORID', 0);
GO

/*[WEBVISITORS]*/

CREATE TABLE WEBVISITORS (
	VISITORID		INT NOT NULL,
	VISITORCODE		NVARCHAR(50) NOT NULL,
	USERID			INT NOT NULL,
	SRCURL			NVARCHAR(1024) NOT NULL,
	CLIENTIPADDR		NVARCHAR(50) NOT NULL,
	CLIENTLOCATION		NVARCHAR(50) NOT NULL,
	CLIENTOS		NVARCHAR(50) NOT NULL,
	WEBBROWSER		NVARCHAR(50) NOT NULL,
	REMARKNAME		NVARCHAR(20) NOT NULL,
	REMARKTEXT		NVARCHAR(1024) NOT NULL,
	FLAG			SMALLINT NOT NULL,
	ISACTIVE		SMALLINT NOT NULL,
	LOGINTIMES		INT NOT NULL,
	LASTLOGINTIME		DATETIME NOT NULL,
	SENDMSGS		INT NOT NULL,
	RECVMSGS		INT NOT NULL,
	LEAVEMSGS		INT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE WEBVISITORS ADD CONSTRAINT PK_WEBVISITORS PRIMARY KEY CLUSTERED (VISITORID, USERID);
GO

CREATE INDEX WEBVISITORS_IDX_ISACTIVE ON WEBVISITORS (ISACTIVE);
GO

CREATE INDEX WEBVISITORS_IDX_CREATETIME ON WEBVISITORS (CREATETIME);
GO

/*[WEBVISITORMESSAGES]*/

CREATE TABLE WEBVISITORMESSAGES (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	VISITORID		INT NOT NULL,
	USERID			INT NOT NULL,
	SENDERISVISITOR		SMALLINT NOT NULL,
	CONTENT			NVARCHAR(1024) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE WEBVISITORMESSAGES ADD CONSTRAINT PK_WEBVISITORMESSAGES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX WEBVISITORMESSAGES_IDX_V_ID ON WEBVISITORMESSAGES (VISITORID);
GO

CREATE INDEX WEBVISITORMESSAGES_IDX_U_ID ON WEBVISITORMESSAGES (USERID);
GO

CREATE INDEX WEBVISITORMESSAGES_IDX_CTIME ON WEBVISITORMESSAGES (CREATETIME);
GO

/*[WEBAPPS]*/

CREATE TABLE WEBAPPS (
    APPID              INT NOT NULL,
    FORUSERID          INT NOT NULL,
    FORUSERTYPE        SMALLINT NOT NULL,
    APPNAME            NVARCHAR(50) NOT NULL,
    CATEGORY           NVARCHAR(50) NOT NULL,
    INTRODUCTION       NVARCHAR(255) NOT NULL,
    APPIMAGE           NVARCHAR(255) NOT NULL,
    APPURL             NVARCHAR(1024) NOT NULL,
    METHOD             SMALLINT NOT NULL,
    POSTDATA           NVARCHAR(1024) NOT NULL,
    POPUP              INT NOT NULL,
    CLIENTWEBBROWSER   SMALLINT NOT NULL,
    WEBBROWSERWIDTH    INT NOT NULL,
    WEBBROWSERHEIGHT   INT NOT NULL,
    SHORTCUT           SMALLINT NOT NULL,
    ORDERVALUE         INT NOT NULL,
    CREATETIME         DATETIME NOT NULL
);
GO

ALTER TABLE WEBAPPS ADD CONSTRAINT PK_WEBAPPS PRIMARY KEY CLUSTERED (APPID);
GO

CREATE INDEX WEBAPPS_IDX_FORUSERID ON WEBAPPS (FORUSERID);
GO

CREATE INDEX WEBAPPS_IDX_FORUSERTYPE ON WEBAPPS (FORUSERTYPE);
GO

CREATE INDEX WEBAPPS_IDX_ORDERVALUE ON WEBAPPS (ORDERVALUE);
GO

CREATE INDEX WEBAPPS_IDX_CREATETIME ON WEBAPPS (CREATETIME);
GO

/*[RECENTITEMS]*/

CREATE TABLE RECENTITEMS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	ITEMTYPE		SMALLINT NOT NULL,
	ITEMVALUE		NVARCHAR(50) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE RECENTITEMS ADD CONSTRAINT PK_RECENTITEMS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX RECENTITEMS_IDX_USERID ON RECENTITEMS (USERID);
GO

CREATE INDEX RECENTITEMS_IDX_CREATETIME ON RECENTITEMS (CREATETIME);
GO

/*[SYSMSGS]*/

CREATE TABLE SYSMSGS (
    SYSMSGID           BIGINT NOT NULL, 
    TITLE              NVARCHAR(50) NOT NULL, 
    CATEGORY           NVARCHAR(50) NOT NULL, 
    CONTENT            NVARCHAR(1024) NOT NULL, 
    LINK               NVARCHAR(255) NOT NULL, 
    ATTACHFILE         NVARCHAR(255) NOT NULL, 
    FORUSERTYPE        SMALLINT NOT NULL,     
    FORDEPARTID        INT NOT NULL, 
    FORDEPARTNAME      NVARCHAR(50) NOT NULL,
    SENDTONUM          INT NOT NULL,
    SENDTODETAIL       NVARCHAR(255) NOT NULL,
    FORONLINEUSERS     SMALLINT NOT NULL,
    NEEDREADED         SMALLINT NOT NULL,
    NEEDFEEDBACK       SMALLINT NOT NULL,
    READEDCOUNT        INT NOT NULL,
    FEEDBACKCOUNT      INT NOT NULL,
    SENDTIME           DATETIME NOT NULL 
);
GO

ALTER TABLE SYSMSGS ADD CONSTRAINT PK_SYSMSGS PRIMARY KEY CLUSTERED (SYSMSGID);
GO

CREATE INDEX SYSMSGS_IDX_FORUSERTYPE ON SYSMSGS (FORUSERTYPE);
GO

CREATE INDEX SYSMSGS_IDX_FORDEPARTID ON SYSMSGS (FORDEPARTID);
GO

CREATE INDEX SYSMSGS_IDX_FORONLINEUSERS ON SYSMSGS (FORONLINEUSERS);
GO

CREATE INDEX SYSMSGS_IDX_SENDTIME ON SYSMSGS (SENDTIME);
GO

/*[SYSMSGDETAILS]*/

CREATE TABLE SYSMSGDETAILS (
    SYSMSGID           BIGINT NOT NULL, 
    USERID             INT NOT NULL,
    READED             SMALLINT NOT NULL,
    FEEDBACK           SMALLINT NOT NULL,
    FEEDBACKTEXT       NVARCHAR(255) NOT NULL, 
    SENDTIME           DATETIME NOT NULL 
);
GO

ALTER TABLE SYSMSGDETAILS ADD CONSTRAINT PK_SYSMSGDETAILS PRIMARY KEY CLUSTERED (SYSMSGID, USERID);
GO

CREATE INDEX SYSMSGDETAILS_IDX_SENDTIME ON SYSMSGDETAILS (SENDTIME);
GO

/*[SENDSMS]*/

CREATE TABLE SENDSMS (
    MSGID              VARCHAR(50) NOT NULL,
    USERID             INT NOT NULL,
    MOBILE             NVARCHAR(20) NOT NULL,
    CONTENT            NVARCHAR(4000) NOT NULL,
    RECEIVER           INT NOT NULL,
    SENDTIME           DATETIME NOT NULL 
);
GO

ALTER TABLE SENDSMS ADD CONSTRAINT PK_SENDSMS PRIMARY KEY CLUSTERED (MSGID);
GO

CREATE INDEX SENDSMS_IDX_USERID ON SENDSMS (USERID);
GO

CREATE INDEX SENDSMS_IDX_RECEIVER ON SENDSMS (RECEIVER);
GO

CREATE INDEX SENDSMS_IDX_SENDTIME ON SENDSMS (SENDTIME);
GO

/*[RECVSMS]*/

CREATE TABLE RECVSMS (
    MSGID              VARCHAR(50) NOT NULL,
    USERID             INT NOT NULL,
    MOBILE             NVARCHAR(20) NOT NULL,
    CONTENT            NVARCHAR(4000) NOT NULL,
    HAVEREAD           SMALLINT NOT NULL,
    SENDTIME           DATETIME NOT NULL 
);
GO

ALTER TABLE RECVSMS ADD CONSTRAINT PK_RECVSMS PRIMARY KEY CLUSTERED (MSGID);
GO

CREATE INDEX RECVSMS_IDX_USERID ON RECVSMS (USERID);
GO

CREATE INDEX RECVSMS_IDX_HAVEREAD ON RECVSMS (HAVEREAD);
GO

CREATE INDEX RECVSMS_IDX_SENDTIME ON RECVSMS (SENDTIME);
GO

/*[ADMINS]*/

CREATE TABLE ADMINS (
	ADMINNAME		NVARCHAR(20) NOT NULL,
	ADMINRIGHTS             NVARCHAR(255) NOT NULL,
	ADMINPWD		NVARCHAR(255) NOT NULL,	
	DESCRIPTION		NVARCHAR(255) NOT NULL,
	LOGINED			SMALLINT NOT NULL,
	ISLOCK			SMALLINT NOT NULL,
	LASTLOGINTIME		DATETIME,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ADMINS ADD CONSTRAINT PK_ADMINS PRIMARY KEY CLUSTERED (ADMINNAME);
GO

CREATE INDEX ADMINS_IDX_ADMINPWD ON ADMINS (ADMINPWD);
GO

CREATE INDEX ADMINS_IDX_LOGINED ON ADMINS (LOGINED);
GO

CREATE INDEX ADMINS_IDX_ISLOCK ON ADMINS (ISLOCK);
GO

CREATE INDEX ADMINS_IDX_CREATETIME ON ADMINS (CREATETIME);
GO

/*[ADMINLOGS]*/

CREATE TABLE ADMINLOGS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	ADMINNAME		NVARCHAR(20) NOT NULL,
	DESCRIPTION		NVARCHAR(255) NOT NULL,
	IPADDR			NVARCHAR(20) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE ADMINLOGS ADD CONSTRAINT PK_ADMINLOGS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX ADMINLOGS_IDX_ADMINNAME ON ADMINLOGS (ADMINNAME);
GO

CREATE INDEX ADMINLOGS_IDX_CREATETIME ON ADMINLOGS (CREATETIME);
GO


/*[OFFLINEFILES]*/

CREATE TABLE OFFLINEFILES (
	SID			VARCHAR(50) NOT NULL,
	SENDER_USERID		INT NOT NULL,
	RECEIVER_USERID		INT NOT NULL,
	FILENAMEORDIR 		NVARCHAR(255) NOT NULL,
	ISDIR			SMALLINT NOT NULL,
        ALLFILECOUNT            INT NOT NULL,
	ALLFILESIZE             BIGINT NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE OFFLINEFILES ADD CONSTRAINT PK_OFFLINEFILES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFLINEFILES_IDX_S_UID ON OFFLINEFILES (SENDER_USERID);
GO

CREATE INDEX OFFLINEFILES_IDX_R_UID ON OFFLINEFILES (RECEIVER_USERID);
GO

CREATE INDEX OFFLINEFILES_IDX_C_TIME ON OFFLINEFILES (CREATETIME);
GO

/*[OFFLINEMESSAGES]*/

CREATE TABLE OFFLINEMESSAGES (
	MSGID			VARCHAR(50) NOT NULL,
	USERID			INT NOT NULL,
	MSGTYPE			SMALLINT NOT NULL,
	MSGLEVEL		SMALLINT NOT NULL,
	[CONTENT] 		TEXT NOT NULL,
	FONT			NVARCHAR(50) NOT NULL,
	SENDER_USERID		INT NOT NULL,
	SENDER_NICKNAME		NVARCHAR(20) NOT NULL,
	SENDER_ACTUALNAME	NVARCHAR(20) NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE OFFLINEMESSAGES ADD CONSTRAINT PK_OFFLINEMESSAGES PRIMARY KEY CLUSTERED (MSGID, USERID);
GO

CREATE INDEX OFFLINEMESSAGES_IDX_S_UID ON OFFLINEMESSAGES (SENDER_USERID);
GO

CREATE INDEX OFFLINEMESSAGES_IDX_S_NNAME ON OFFLINEMESSAGES (SENDER_NICKNAME);
GO

CREATE INDEX OFFLINEMESSAGES_IDX_S_TIME ON OFFLINEMESSAGES (SENDTIME);
GO

/*[OFFLINESUPEROBJECTS]*/

CREATE TABLE OFFLINESUPEROBJECTS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,	
	MSGID			VARCHAR(50) NOT NULL,
	FROMUSERID		INT NOT NULL,
	SUPEROBJECTCODE		NVARCHAR(1024) NOT NULL,
	LOCALFILENAME		NVARCHAR(1024) NOT NULL,
	CREATETIME		DATETIME NOT NULL
);
GO

ALTER TABLE OFFLINESUPEROBJECTS ADD CONSTRAINT PK_OFFLINESUPEROBJECTS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX OFFLINESUPEROBJECTS_IDX_MSGID ON OFFLINESUPEROBJECTS (MSGID);
GO

CREATE INDEX OFFLINESUPEROBJECTS_IDX_F_UID ON OFFLINESUPEROBJECTS (FROMUSERID);
GO

CREATE INDEX OFFLINESUPEROBJECTS_IDX_C_TIME ON OFFLINESUPEROBJECTS (CREATETIME);
GO

/*[JOINREQUESTS]*/

CREATE TABLE JOINREQUESTS (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINREQUESTS ADD CONSTRAINT PK_JOINREQUESTS PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINREQUESTS_IDX_USERID ON JOINREQUESTS (USERID);
GO

CREATE INDEX JOINREQUESTS_IDX_SENDER ON JOINREQUESTS (SENDER);
GO

CREATE INDEX JOINREQUESTS_IDX_SENDTIME ON JOINREQUESTS (SENDTIME);
GO

/*[JOINRESPONSES]*/

CREATE TABLE JOINRESPONSES (
	SID			BIGINT IDENTITY(1,1) NOT NULL,
	USERID			INT NOT NULL,
	CONTENT 		NVARCHAR(255) NOT NULL,
	AGREE			SMALLINT NOT NULL,
	SENDER			INT NOT NULL,
	SENDTIME		DATETIME NOT NULL
);
GO

ALTER TABLE JOINRESPONSES ADD CONSTRAINT PK_JOINRESPONSES PRIMARY KEY CLUSTERED (SID);
GO

CREATE INDEX JOINRESPONSES_IDX_USERID ON JOINRESPONSES (USERID);
GO

CREATE INDEX JOINRESPONSES_IDX_SENDER ON JOINRESPONSES (SENDER);
GO

CREATE INDEX JOINRESPONSES_IDX_SENDTIME ON JOINRESPONSES (SENDTIME);
GO

/*[PUSH]*/

CREATE TABLE PUSH (
    PUSHID          INT NOT NULL,
    USERID          INT NOT NULL,
    CLIENTID        VARCHAR(50) NOT NULL,
    DEVICE          VARCHAR(50),
    OS              SMALLINT NOT NULL,
    BADGE           INT,
    IOSDEVICETOKEN  VARCHAR(100),
    ISACTIVE        SMALLINT,
    CREATETIME      DATETIME,
    NEEDPUSH        SMALLINT
);
GO

ALTER TABLE PUSH ADD CONSTRAINT PK_PUSH PRIMARY KEY (PUSHID);
GO

/*----------------------------------------------Procedures---------------------------------------------*/

--ACCOUNT--

/* GETACCOUNTSECTIONS */
/* REMOVEACCOUNTSECTION */
/* ADDACCOUNTS */
/* REMOVEACCOUNTS */
/* REGISTERACCOUNT */
/* COUNTACCOUNTS */
/* SEARCHACCOUNTS */

--DEPARTMENT--

/* ADDDEPARTMENT */
/* ADDDEPARTMENTEX */
/* MODIFYDEPARTMENT */
/* MOVEDEPARTMENT */
/* SETDEPARTMENTORDERVALUE */
/* GETDEPARTMENT */
/* GETDEPARTMENTS */
/* GETDEPARTMENTUSERS */

--USER--

/* EXISTSUSER */
/* ADDUSEREX */
/* CHANGEUSERDEPARTMENT */
/* GETWHOJOINMEIDS */
/* ISJOINME */
/* CHANGELOGINSTATUS */
/* VALIDATEPASSWORD */
/* CHANGEPASSWORD */
/* RESETPASSWORD */
/* MODIFYUSER */
/* CHANGESIGNATURE */
/* CHANGEUSERINFO */
/* CHANGEFACEFILE */
/* CHANGEPHOTOFILE */
/* CHANGELINKDEPART */
/* GETUSERBYID */
/* GETUSERBYNAME */
/* GETUSERBYTOKEN */
/* LOCKUSER */
/* LOGIN */
/* LOGOUT */
/* REMOVEUSER */
/* RESETLOGINSTATUS */
/* UPDATESMSSETTING */
/* UPDATESMSSENDCOUNT */

/* UPDATEUSERJOINSETTING */
/* GETUSERJOINSETTING */
/* VALIDATEUSERJOINQUESTION */

/* GETPWDPROTECTION */
/* SETPWDPROTECTION */
/* VALIDATEPWDPROTECTION */

/* SEARCHUSERS */
/* SEARCHPUBLICUSERS */
/* SEARCHENTERPRISEUSERS */
/* COUNTPUBLICUSERS */
/* COUNTENTERPRISEUSERS */

--CONTACT--

/* ADDCONTACTGROUP */
/* REMOVECONTACTGROUP */
/* CHANGECONTACTGROUPNAME */
/* CHANGECONTACTGROUPSETTING */
/* GETCONTACTGROUPS */
/* ADDCONTACT */
/* REMOVECONTACT */
/* MOVECONTACT */
/* MOVETOBLACKLIST */
/* CHANGECONTACTREMARKNAME */
/* CHANGECONTACTSETTING */
/* GETCONTACTSETTINGS */
/* GETCONTACTS */

--ADDRLST--

/* ADDADDRLSTCATEGORY */
/* ADDADDRLSTCATEGORYEX */
/* MODIFYADDRLSTCATEGORY */
/* SETADDRLSTCATEGORYORDERVALUE */
/* GETADDRLSTCATEGORY */
/* GETADDRLSTCATEGORYS */
/* ADDADDRLSTITEM */
/* MODIFYADDRLSTITEM */
/* CHANGEADDRLSTITEMCATEGORY */
/* REMOVEADDRLSTITEM */
/* GETADDRLSTITEMS */

--GROUP--

/* ADDGROUP */
/* MODIFYGROUP */
/* REMOVEGROUP */
/* CHANGEGROUPOWNER */
/* SETGROUPMANAGER */
/* GETUSERGROUPS */
/* GETALLGROUPS */

/* ADDGROUPMEMBER */
/* REMOVEGROUPMEMBER */
/* MODIFYGROUPMEMBER */
/* UPDATEGROUPLASTRECVMSGSID */
/* GETGROUPMEMBERS */

/* ADDGROUPSHAREFILES */
/* REMOVEGROUPSHAREFILES */
/* GETGROUPSHAREFILES */

/* ADDJOINGROUPREQUEST */
/* GETJOINGROUPREQUESTS */
/* ADDJOINGROUPRESPONSE */
/* GETJOINGROUPRESPONSES */

/* ADDOFFGROUPMSG */
/* REMOVEOFFGROUPMSG */
/* GETOFFGROUPMSGS */

/* ADDOFFGROUPSUPEROBJ */
/* REMOVEOFFGROUPSUPEROBJ */
/* GETOFFGROUPSUPEROBJS */

--ROOM--

/* ADDROOM */
/* MODIFYROOM */
/* REMOVEROOM */
/* CHANGEROOMOWNER */
/* SETROOMMANAGER */
/* GETUSERROOMS */
/* GETALLROOMS */

/* ADDROOMMEMBER */
/* REMOVEROOMMEMBER */
/* MODIFYROOMMEMBER */
/* UPDATEROOMLASTRECVMSGSID */
/* GETROOMMEMBERS */

/* ADDROOMSHAREFILES */
/* REMOVEROOMSHAREFILES */
/* GETROOMSHAREFILES */

/* ADDJOINROOMREQUEST */
/* GETJOINROOMREQUESTS */
/* ADDJOINROOMRESPONSE */
/* GETJOINROOMRESPONSES */

/* ADDOFFROOMMSG */
/* REMOVEOFFROOMMSG */
/* GETOFFROOMMSGS */

/* ADDOFFROOMSUPEROBJ */
/* REMOVEOFFROOMSUPEROBJ */
/* GETOFFROOMSUPEROBJS */

--WEBVISITOR--

/* GETWEBVISITORID */
/* ADDWEBVISITOR */
/* REMOVEWEBVISITOR */
/* CHANGEWEBVISITORREMARKNAME */
/* CHANGEWEBVISITORREMARKTEXT */
/* WEBVISITORINACTIVE */
/* WEBVISITORINCSENDMSGS */
/* WEBVISITORINCRECVMSGS */
/* WEBVISITORINCLEAVEMSGS */
/* GETINACTIVEWEBVISITORS */
/* ADDWEBVISITORMESSAGE */
/* GETRECENTWEBVISITORMESSAGES */
/* GETWEBVISITORMESSAGES */

--WEBAPP--

/* ADDWEBAPP */
/* REMOVEWEBAPP */
/* MODIFYWEBAPP */
/* CHANGEWEBAPPIMAGE */
/* CHANGEWEBAPPORDERVALUE */
/* GETWEBAPP */
/* GETUSERWEBAPPS */

--RECENT--

/* ADDRECENTITEM */
/* REMOVERECENTITEM */
/* GETRECENTITEMS */
/* ADDOFFLINEFILES */
/* REMOVEOFFLINEFILES */
/* GETOFFLINEFILES */

--SYSMSG--

/* ADDSYSMSG */
/* GETUSERSYSMSGS */
/* GETSYSMSGS */
/* REMOVESYSMSG */
/* ADDSYSMSGDETAIL */
/* CHANGESYSMSGDETAIL_READED */
/* CHANGESYSMSGDETAIL_FEEDBACK */
/* GETSYSMSGDETAILS */

--SMS--

/* ADDSENDSMS */
/* GETSENDSMSLIST */
/* ADDRECVSMS */
/* GETRECVSMSLIST */

--ADMIN--

/* ADDADMIN */
/* MODIFYADMIN */
/* VALIDATEADMIN */
/* ADMINLOGIN */
/* ADMINLOGOUT */
/* LOCKADMIN */
/* CHANGEADMINPWD */
/* REMOVEADMIN */
/* GETADMIN */
/* GETADMINS */
/* RESETLOGINEDADMINS */

/* ADDADMINLOG */
/* GETADMINLOGS */

--CHAT--

/* ADDJOINREQUEST */
/* GETJOINREQUESTS */
/* ADDJOINRESPONSE */
/* GETJOINRESPONSES */

/* ADDOFFLINEMESSAGE */
/* REMOVEOFFLINEMESSAGE */
/* GETOFFLINEMESSAGES */

/* ADDOFFLINESUPEROBJECT */
/* REMOVEOFFLINESUPEROBJECT */
/* GETOFFLINESUPEROBJECTS */


/*----------------------------------------------Procedures---------------------------------------------*/

/* GETACCOUNTSECTIONS */

CREATE PROCEDURE GETACCOUNTSECTIONS 
/*
returns (
    @out_beginuserid int,
    @out_enduserid int,
    @out_createtime datetime)
*/
as
begin
  select BEGINUSERID as out_beginuserid, ENDUSERID as out_enduserid, CREATETIME as out_createtime 
    from ACCOUNTSECTIONS where 1 = 1 order by CREATETIME
end;
GO

/* ADDACCOUNTS */

CREATE PROCEDURE ADDACCOUNTS
    @in_beginuserid int,
    @in_enduserid int
/*
returns (
    @out_errorcode int,
    @out_completed int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;
  declare @out_completed int;

  declare @var_tempuserid int;
  declare @var_curuserid int;
  declare @var_section nvarchar(50);

  set @out_errorcode = 0;

  set @out_completed = 0;

  if ((@in_beginuserid <= 0) or (@in_enduserid < @in_beginuserid))  
  begin
    set @out_errorcode = 1;
    goto result_label;
  end;

  if (exists(select * from ACCOUNTSECTIONS where BEGINUSERID <= @in_beginuserid and ENDUSERID >= @in_beginuserid) or 
    exists(select * from ACCOUNTSECTIONS where BEGINUSERID <= @in_enduserid and ENDUSERID >= @in_enduserid)) 
  begin
    set @out_errorcode = 2;
    goto result_label;
  end;

  insert into ACCOUNTSECTIONS(BEGINUSERID, ENDUSERID, CREATETIME) values(@in_beginuserid, @in_enduserid, getdate());

  set @var_section = cast(@in_beginuserid as nvarchar(20)) + '-' + cast(@in_enduserid as nvarchar(20));

  set @var_curuserid = @in_beginuserid;
  while (@var_curuserid <= @in_enduserid)
  begin
    if (not exists(select * from ACCOUNTS where userid = @var_curuserid)) 
    begin
      insert into ACCOUNTS(
        USERID,
	RANDOMVALUE,
	REGISTED,
        REGISTERTIME,
	REGISTERIPADDR,
	SECTION,
        CREATETIME
        )
        values(
        @var_curuserid,
	rand(),
	0,        
	null,
	'',
	@var_section,
        getdate()
        );

      set @out_completed = @out_completed + 1;
    end;
    set @var_curuserid = @var_curuserid + 1;
  end;

result_label: 
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode,  @out_completed as out_completed;
  return;
end;
GO

/* REMOVEACCOUNTS */

CREATE PROCEDURE REMOVEACCOUNTS 
    @in_beginuserid int,
    @in_enduserid int
as
begin
  SET NOCOUNT ON;

  delete from ACCOUNTSECTIONS where BEGINUSERID = @in_beginuserid and ENDUSERID = @in_enduserid;
  delete from ACCOUNTS where (USERID >= @in_beginuserid) and (USERID <= @in_enduserid) and (REGISTED = 0);

  SET NOCOUNT OFF;
end;
GO

/* REGISTERACCOUNT */

CREATE PROCEDURE REGISTERACCOUNT 
    @in_ipaddr nvarchar(50)
/*
returns (
    @out_errorcode int,
    @out_userid int,
    @out_registed smallint,
    @out_registertime datetime,
    @out_registeripaddr nvarchar(50),
    @out_section nvarchar(50),
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_24hoursbefore datetime;
  declare @var_now datetime;
  declare @var_regnumthisday int;
  declare @var_newuserid int;

  set @out_errorcode = 0;

  set @var_now = getdate();
  set @var_24hoursbefore = dateadd(hour, -24, @var_now);

  select @var_regnumthisday = count(*) from ACCOUNTS where (REGISTED = 1) and (REGISTERIPADDR = @in_ipaddr) and 
    ((REGISTERTIME >= @var_24hoursbefore) and (REGISTERTIME <= @var_now));

  if (@var_regnumthisday >= 10) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end;

  select top 1 @var_newuserid = USERID from ACCOUNTS 
      where (REGISTED = 0) order by RANDOMVALUE;

  if (not (@var_newuserid is null)) 
  begin
    update ACCOUNTS set REGISTED = 1, REGISTERTIME = getdate(), REGISTERIPADDR = @in_ipaddr where USERID = @var_newuserid;
  end;

  if (not (@var_newuserid is null)) 
  begin
    goto result_label2;
  end
  else
  begin
    set @out_errorcode = 2;
    goto result_label;
  end;

result_label: 
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2: 
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, USERID as out_userid, REGISTED as out_registed, 
        REGISTERTIME as out_registertime, REGISTERIPADDR as out_registeripaddr, 
	SECTION as out_section, CREATETIME as out_createtime 
	from ACCOUNTS where USERID = @var_newuserid;
  return;
end;
GO

/* COUNTACCOUNTS */

CREATE PROCEDURE COUNTACCOUNTS 
/*
returns (
    @out_count int,
    @out_registed smallint)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_count int;
  declare @out_registed smallint;

  select @out_count = count(*) from ACCOUNTS where 1 = 1;
  select @out_registed = count(*) from ACCOUNTS where (REGISTED = 1);

  SET NOCOUNT OFF;

  select @out_count as out_count, @out_registed as out_registed;
end;
GO

/* SEARCHACCOUNTS */

CREATE PROCEDURE SEARCHACCOUNTS 
    @in_beginuserid int,
    @in_enduserid int,
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_maxpageindex int,
    @out_userid int,
    @out_registed smallint,
    @out_registertime datetime,
    @out_registeripaddr nvarchar(50),
    @out_section nvarchar(50),
    @out_createtime datetime)
*/
as
begin
  declare @out_maxpageindex int;

  declare @var_accountcount int;
  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(4000);
  declare @var_paging_sql nvarchar(4000);

  if ((@in_beginuserid > 0) and (@in_enduserid > 0)) 
  begin
	  select @var_accountcount = count(*) from ACCOUNTS where 
		USERID >= @in_beginuserid and USERID <= @in_enduserid;
  end
  else
  begin
	  select @var_accountcount = count(*) from ACCOUNTS where 1 = 1;
  end;

  set @out_maxpageindex = @var_accountcount / @in_pagesize;
  if ((@var_accountcount % @in_pagesize) > 0) 
  begin
    set @out_maxpageindex = @out_maxpageindex + 1;
  end;

  if (@out_maxpageindex <= 0) set @out_maxpageindex = 1;

  if ((@in_pageindex > @out_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @out_maxpageindex;
  end

  if ((@in_beginuserid > 0) and (@in_enduserid > 0)) 
  begin
    set @var_where_sql = 
      '(USERID >= ' + cast(@in_beginuserid as nvarchar(20)) + ')' +
      ' and (USERID <= ' + cast(@in_enduserid as nvarchar(20)) + ')';
  end
  else
  begin
    set @var_where_sql = '(1 = 1)';
  end;

  set @var_where_sql = @var_where_sql + ' order by USERID DESC';

  set @var_fields_sql = 
      str(@out_maxpageindex) + ' as out_maxpageindex, USERID as out_userid, REGISTED as out_registed,' +
      ' REGISTERTIME as out_registertime, REGISTERIPADDR as out_registeripaddr,' +
      ' SECTION as out_section, CREATETIME as out_createtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from ACCOUNTS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from ACCOUNTS where ' +
          ' (USERID < (select min(USERID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' USERID from ACCOUNTS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  exec(@var_paging_sql);
end;
GO

/* ADDDEPARTMENT */

CREATE PROCEDURE ADDDEPARTMENT 
    @in_departname nvarchar(50),
    @in_parentdepartid int
/*
returns (
    @out_departid int,
    @out_departname nvarchar(50),
    @out_ordervalue int,
    @out_parentdepartid int,
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_newdepartid int;
  declare @var_ordervalue int;

  select @var_newdepartid = max(DEPARTID) from DEPARTMENTS;
  if (@var_newdepartid is null)  set @var_newdepartid = 0;
  set @var_newdepartid = @var_newdepartid + 1;

  select @var_ordervalue = max(ORDERVALUE) from DEPARTMENTS;
  if (@var_ordervalue is null)  set @var_ordervalue = 0;
  set @var_ordervalue = @var_ordervalue + 1;

  insert into DEPARTMENTS(
    DEPARTID,
    DEPARTNAME,
    ORDERVALUE,
    PARENTDEPARTID,
    CREATETIME
    )
    values(
    @var_newdepartid,
    @in_departname,
    @var_ordervalue,
    @in_parentdepartid,
    getdate()
    );

  SET NOCOUNT OFF;

  select DEPARTID as out_departid, DEPARTNAME as out_departname, ORDERVALUE as out_ordervalue, 
       PARENTDEPARTID as out_parentdepartid, CREATETIME as out_createtime
       from DEPARTMENTS where DEPARTID = @var_newdepartid;
end;
GO

/* ADDDEPARTMENTEX */

CREATE PROCEDURE ADDDEPARTMENTEX 
    @in_departid int,
    @in_departname nvarchar(50),
    @in_ordervalue int,
    @in_parentdepartid int
/*
returns (
    @out_errorcode int,
    @out_departid int,
    @out_departname nvarchar(50),
    @out_ordervalue int,
    @out_parentdepartid int,
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (exists(select * from DEPARTMENTS where DEPARTID = @in_departid)) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  insert into DEPARTMENTS(
    DEPARTID,
    DEPARTNAME,
    ORDERVALUE,
    PARENTDEPARTID,
    CREATETIME
    )
    values(
    @in_departid,
    @in_departname,
    @in_ordervalue,
    @in_parentdepartid,
    getdate()
    );

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, DEPARTID as out_departid, DEPARTNAME as out_departname, 
       ORDERVALUE as out_ordervalue, PARENTDEPARTID as out_parentdepartid, 
       CREATETIME as out_createtime from DEPARTMENTS where DEPARTID = @in_departid;
  return;
end;
GO

/* MODIFYDEPARTMENT */

CREATE PROCEDURE MODIFYDEPARTMENT 
    @in_departid int,
    @in_departname nvarchar(50)
as
begin
  SET NOCOUNT ON;

  update DEPARTMENTS set DEPARTNAME = @in_departname where DEPARTID = @in_departid;
  update USERS set DEPARTNAME = @in_departname where DEPARTID = @in_departid;

  SET NOCOUNT OFF;
end;
GO

/* MOVEDEPARTMENT */

CREATE PROCEDURE MOVEDEPARTMENT (
    @in_departid int,
    @in_parentdepartid int)
as
    declare @var_ordervalue int;
begin
  select @var_ordervalue = max(ORDERVALUE) from DEPARTMENTS where PARENTDEPARTID = @in_parentdepartid;
  if (@var_ordervalue is null)
  begin
    set @var_ordervalue = 0;
  end
  else
  begin
    set @var_ordervalue = @var_ordervalue + 1;
  end
  
  update DEPARTMENTS set PARENTDEPARTID = @in_parentdepartid, ORDERVALUE = @var_ordervalue where DEPARTID = @in_departid;
end;
GO

/* SETDEPARTMENTORDERVALUE */

CREATE PROCEDURE SETDEPARTMENTORDERVALUE 
    @in_departid int,
    @in_ordervalue int
as
begin
  SET NOCOUNT ON;

  update DEPARTMENTS set ORDERVALUE = @in_ordervalue where DEPARTID = @in_departid;
 
  SET NOCOUNT OFF;
end;
GO

/* GETDEPARTMENT */

CREATE PROCEDURE GETDEPARTMENT 
    @in_departid int
/*
returns (
    @out_departid int,
    @out_departname nvarchar(50),
    @out_ordervalue int,
    @out_parentdepartid int,
    @out_createtime datetime)
*/
as
begin
  select DEPARTID as out_departid, DEPARTNAME as out_departname, 
       ORDERVALUE as out_ordervalue, PARENTDEPARTID as out_parentdepartid, CREATETIME as out_createtime
       from DEPARTMENTS where DEPARTID = @in_departid;
end;
GO

/* GETDEPARTMENTS */

CREATE PROCEDURE GETDEPARTMENTS 
    @in_parentdepartid int
/*
returns (
    @out_departid int,
    @out_departname nvarchar(50),
    @out_ordervalue int,
    @out_parentdepartid int,
    @out_createtime datetime)
*/
as
begin
  if (@in_parentdepartid >= 0) 
  begin
    select DEPARTID as out_departid, DEPARTNAME as out_departname,
      ORDERVALUE as out_ordervalue, PARENTDEPARTID as out_parentdepartid, CREATETIME as out_createtime
      from DEPARTMENTS where PARENTDEPARTID = @in_parentdepartid
      order by PARENTDEPARTID ASC, ORDERVALUE ASC;
  end
  else
  begin
    select DEPARTID as out_departid, DEPARTNAME as out_departname, 
      ORDERVALUE as out_ordervalue, PARENTDEPARTID as out_parentdepartid, CREATETIME as out_createtime
      from DEPARTMENTS where 1 = 1 order by PARENTDEPARTID ASC, ORDERVALUE ASC;
  end
end;
GO

/* GETDEPARTMENTUSERS */

CREATE PROCEDURE GETDEPARTMENTUSERS
    @in_departid int
/*
returns (    
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_userposition int,

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,
    
    @out_modifytime datetime)
*/
as
begin
  if (@in_departid > 0)
  begin
	  select USERID as out_userid, USERNAME as out_USERNAME, USERTYPE as out_usertype, CANFOUND as out_canfound, 
	       NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age,
	       BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, 
	       COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area,
	       ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, 
	       TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, 
	       MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, 
	       WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, 
	       CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, 
	       JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, PHOTOFILE as out_photofile,
               LOGINSTATUS as out_loginstatus, USERPOSITION as out_userposition, SMSTOTAL as out_smstotal, SMSDAYLIMIT as out_smsdaylimit, 
	       SMSDAYSTART as out_smsdaystart, SMSDAYSENDED as out_smsdaysended, SMSTOTALSENDED as out_smstotalsended, MODIFYTIME as out_modifytime
	  from USERS where DEPARTID = @in_departid order by USERID ASC;

  end
  else
  begin
	  select USERID as out_userid, USERNAME as out_USERNAME, USERTYPE as out_usertype, CANFOUND as out_canfound, 
	       NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age,
	       BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, 
	       COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area,
	       ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, 
	       TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, 
	       MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, 
	       WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, 
	       CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, 
	       JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, PHOTOFILE as out_photofile,
               LOGINSTATUS as out_loginstatus, USERPOSITION as out_userposition, SMSTOTAL as out_smstotal, SMSDAYLIMIT as out_smsdaylimit, 
	       SMSDAYSTART as out_smsdaystart, SMSDAYSENDED as out_smsdaysended, SMSTOTALSENDED as out_smstotalsended, MODIFYTIME as out_modifytime
	  from USERS where DEPARTID > 0 order by USERID ASC;
  end;
end;
GO

/* EXISTSUSER */

CREATE PROCEDURE EXISTSUSER 
    @in_userid int
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;
  set @out_errorcode = 0;
  if (exists(select * from USERS where USERID = @in_userid)) 
  begin
    set @out_errorcode = 1;
  end;

  SET NOCOUNT OFF;

  select @out_errorcode as out_errorcode;
end;
GO

/* ADDUSEREX */

CREATE PROCEDURE ADDUSEREX 
    @in_userid int,
    @in_username nvarchar(50),
    @in_usertype smallint,
    @in_canfound smallint,

    @in_nickname nvarchar(20),
    @in_signature nvarchar(255),
    @in_actualname nvarchar(20),
    @in_sex smallint,
    @in_age smallint,
    @in_birth_year smallint,
    @in_birth_month smallint,
    @in_birth_day smallint,
    @in_country smallint,
    @in_province smallint,
    @in_city smallint,
    @in_area smallint,
    @in_address nvarchar(50),
    @in_telephone nvarchar(20),
    @in_telephone_short nvarchar(20),
    @in_telephone2 nvarchar(20),
    @in_telephone2_short nvarchar(20),
    @in_mobile nvarchar(20),
    @in_mobile_short nvarchar(20),
    @in_mobile2 nvarchar(20),
    @in_mobile2_short nvarchar(20),
    @in_fax nvarchar(20),
    @in_qq nvarchar(50),
    @in_weixin nvarchar(50),
    @in_weibo nvarchar(50),
    @in_email nvarchar(50),
    @in_roomname nvarchar(50),
    @in_roomnumber nvarchar(50),
    @in_idnumber nvarchar(50),
    @in_carnumber nvarchar(50),
    @in_homepage nvarchar(80),
    @in_departid int,
    @in_departname nvarchar(50),
    @in_jobtitle nvarchar(50),
    @in_jobnumber nvarchar(20),
    @in_introduction nvarchar(255),

    @in_facefile nvarchar(255),
    @in_photofile nvarchar(255),

    @in_pwd nvarchar(255),
    @in_salt nvarchar(6),

    @in_smstotal int,
    @in_smsdaylimit int
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;
  
  if (exists(select * from users where userid = @in_userid)) 
  begin
    set @out_errorcode = 1; /*--userid exists--*/
    goto result_babel;
  end;

  if (@in_username != '') 
  begin
    if (exists(select * from users where username = @in_username)) 
    begin
      set @out_errorcode = 2; /*--username exists--*/
      goto result_babel;
    end;
  end;

  declare @var_userposition int;
  set @var_userposition = 0;
  if ((@in_usertype = 1) and (@in_departid > 0))
  begin
    select @var_userposition = max(USERPOSITION) from users where DEPARTID = @in_departid;
	if (@var_userposition is null) set @var_userposition = -1;
	set @var_userposition = @var_userposition + 1;
  end

  insert into USERS(
	USERID,
	USERNAME,
	USERTYPE,
	CANFOUND,

	NICKNAME,
	SIGNATURE,
	ACTUALNAME,
	SEX,
	AGE,
	BIRTH_YEAR,
	BIRTH_MONTH,
	BIRTH_DAY,
	COUNTRY,
	PROVINCE,
	CITY,
	AREA,
	ADDRESS,
	TELEPHONE,
	TELEPHONE_SHORT,
	TELEPHONE2,
	TELEPHONE2_SHORT,
	MOBILE,
	MOBILE_SHORT,
	MOBILE2,
	MOBILE2_SHORT,
	FAX,
	QQ,
	WEIXIN,
	WEIBO,
	EMAIL,
	ROOMNAME,
	ROOMNUMBER,
	IDNUMBER,
	CARNUMBER,
	HOMEPAGE,
	DEPARTID,
	DEPARTNAME,
	JOBTITLE,
	JOBNUMBER,
	INTRODUCTION,

	FACEFILE,
	PHOTOFILE,

	LOGINSTATUS,
	LOGINTIMES,
	LASTLOGINTIME,
	CLIENTIPADDR,
	CLIENTLOCATION,
	LASTCLIENTIPADDR,
	LASTCLIENTLOCATION,

	PWD,
	SALT,
	TOKEN,
	TOKENUPDATETIME,
	LOCKED,

	ONLYFINDMEBYID,
	JOINSETTING,
	JOINQUESTION,
	JOINANSWER,

	LASTRECVSYSMSGS,
	LINKDEPARTID,
	USERPOSITION,

	SMSTOTAL,
	SMSDAYLIMIT,
	SMSDAYSTART,
	SMSDAYSENDED,
	SMSTOTALSENDED,  

	MODIFYTIME,
	CREATETIME
    )
    values(
	@in_userid,
	@in_username,
	@in_usertype,
	@in_canfound,

	@in_nickname,
	@in_signature,
	@in_actualname,
	@in_sex,
	@in_age,
	@in_birth_year,
	@in_birth_month,
	@in_birth_day,
	@in_country,
	@in_province,
	@in_city,
	@in_area,
	@in_address,
	@in_telephone,
	@in_telephone_short,
	@in_telephone2,
	@in_telephone2_short,
	@in_mobile,
	@in_mobile_short,
	@in_mobile2,
	@in_mobile2_short,
	@in_fax,
	@in_qq,
	@in_weixin,
	@in_weibo,
	@in_email,
	@in_roomname,
	@in_roomnumber,
	@in_idnumber,
	@in_carnumber,
	@in_homepage,
	@in_departid,
	@in_departname,
	@in_jobtitle,
	@in_jobnumber,
	@in_introduction,

	@in_facefile,		
	@in_photofile,		

	0,		/*loginstatus*/
	0,		/*logintimes*/
	null,		/*lastlogintime*/
	'',		/*clientipaddr*/
	'',		/*clientlocation*/
	'',		/*lastclientipaddr*/
	'',		/*lastclientlocation*/

	@in_pwd,	/*pwd*/
	@in_salt,	/*salt*/
	'',		/*token*/
	null,		/*tokenupdatetime*/
	0,		/*lock*/

	0,		/*onlyfindmebyid*/
	0,		/*joinsetting*/
	'',		/*joinquestion*/
	'',		/*joinanswer*/

	null,        /*lastrecvsysmsgs*/
	0,            /*linkdepartid*/
	@var_userposition,            /*userposition*/

	@in_smstotal,
	@in_smsdaylimit,
	getdate(),
	0,
	0,

	getdate(),	/*modifytime*/
	getdate()	/*createtime*/
    );

result_babel:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* CHANGEUSERDEPARTMENT */

CREATE PROCEDURE CHANGEUSERDEPARTMENT 
    @in_userid int,
    @in_departid int,
    @in_departname nvarchar(50)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    DEPARTID = @in_departid,
    DEPARTNAME = @in_departname,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETWHOJOINMEIDS */

CREATE PROCEDURE GETWHOJOINMEIDS 
    @in_userid int
/*
returns (
    @out_userid int)
*/
as
begin
  select distinct USERID as out_userid from CONTACTS 
    where USERID = @in_userid or CONTACTID = @in_userid;
end;
GO

/* ISJOINME */

CREATE PROCEDURE ISJOINME 
    @in_userid int,
    @in_contactid int
/*
returns (
    @out_status int)
*/
as
begin
  declare @out_status int;
  set @out_status = 0;
  if (exists(select * from CONTACTS 
    where USERID = @in_contactid or CONTACTID = @in_userid))  
  begin
    set @out_status = 1;
  end;
  select @out_status as out_status;
end;
GO

/* CHANGELOGINSTATUS */

CREATE PROCEDURE CHANGELOGINSTATUS 
    @in_userid int,
    @in_loginstatus smallint
as 
begin
  SET NOCOUNT ON;

  update USERS set 
    LOGINSTATUS = @in_loginstatus
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* VALIDATEPASSWORD */

CREATE PROCEDURE VALIDATEPASSWORD 
    @in_userid int,
    @in_password nvarchar(255)
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (not exists(select * from USERS where USERID = @in_userid and PWD = @in_password)) 
  begin
    set @out_errorcode = 1; /*---(not find user) or (password error)---*/
    goto result_label;
  end

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* CHANGEPASSWORD */

CREATE PROCEDURE CHANGEPASSWORD 
    @in_userid int,
    @in_oldpwd nvarchar(255),
    @in_newpwd nvarchar(255)
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (not exists(select * from USERS where USERID = @in_userid and PWD = @in_oldpwd)) 
  begin
    set @out_errorcode = 1; /*---(not find user) or (oldpwd error)---*/
    goto result_label;
  end

  update USERS set PWD = @in_newpwd where USERID = @in_userid;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* RESETPASSWORD */

CREATE PROCEDURE RESETPASSWORD 
    @in_userid int,
    @in_newpwd nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set  PWD = @in_newpwd where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* MODIFYUSER */

CREATE PROCEDURE MODIFYUSER 
    @in_userid int,
    @in_canfound smallint,
    @in_nickname nvarchar(20),
    @in_signature nvarchar(255),
    @in_actualname nvarchar(20),
    @in_sex smallint,
    @in_age smallint,
    @in_birth_year smallint,
    @in_birth_month smallint,
    @in_birth_day smallint,
    @in_country smallint,
    @in_province smallint,
    @in_city smallint,
    @in_area smallint,
    @in_address nvarchar(50),
    @in_telephone nvarchar(20),
    @in_telephone_short nvarchar(20),
    @in_telephone2 nvarchar(20),
    @in_telephone2_short nvarchar(20),
    @in_mobile nvarchar(20),
    @in_mobile_short nvarchar(20),
    @in_mobile2 nvarchar(20),
    @in_mobile2_short nvarchar(20),
    @in_fax nvarchar(20),
    @in_qq nvarchar(50),
    @in_weixin nvarchar(50),
    @in_weibo nvarchar(50),
    @in_email nvarchar(50),
    @in_roomname nvarchar(50),
    @in_roomnumber nvarchar(50),
    @in_idnumber nvarchar(50),
    @in_carnumber nvarchar(50),
    @in_homepage nvarchar(80),
    @in_departid int,
    @in_departname nvarchar(50),
    @in_jobtitle nvarchar(50),
    @in_jobnumber nvarchar(20),
    @in_introduction nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    CANFOUND = @in_canfound,
    NICKNAME = @in_nickname,
    SIGNATURE = @in_signature,
    ACTUALNAME = @in_actualname,
    SEX = @in_sex,
    AGE = @in_age,
    BIRTH_YEAR = @in_birth_year,
    BIRTH_MONTH = @in_birth_month,
    BIRTH_DAY = @in_birth_day,
    COUNTRY = @in_country,
    PROVINCE = @in_province,
    CITY = @in_city,
    AREA = @in_area,
    ADDRESS = @in_address,
    TELEPHONE = @in_telephone,
    TELEPHONE_SHORT = @in_telephone_short,
    TELEPHONE2 = @in_telephone2,
    TELEPHONE2_SHORT = @in_telephone2_short,
    MOBILE = @in_mobile,
    MOBILE_SHORT = @in_mobile_short,
    MOBILE2 = @in_mobile2,
    MOBILE2_SHORT = @in_mobile2_short,
    FAX = @in_fax,
    QQ = @in_qq,
    WEIXIN = @in_weixin,
    WEIBO = @in_weibo,
    EMAIL = @in_email,
    ROOMNAME = @in_roomname,
    ROOMNUMBER = @in_roomnumber,
    IDNUMBER = @in_idnumber,
    CARNUMBER = @in_carnumber,
    HOMEPAGE = @in_homepage,
    DEPARTID = @in_departid,
    DEPARTNAME = @in_departname,
    JOBTITLE = @in_jobtitle,
    JOBNUMBER = @in_jobnumber,
    INTRODUCTION = @in_introduction,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGESIGNATURE */

CREATE PROCEDURE CHANGESIGNATURE 
    @in_userid int,
    @in_signature nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    SIGNATURE = @in_signature,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEUSERINFO */

CREATE PROCEDURE CHANGEUSERINFO 
    @in_userid int,

    @in_nickname nvarchar(20),
    @in_signature nvarchar(255),
    @in_actualname nvarchar(20),
    @in_sex smallint,
    @in_age smallint,
    @in_birth_year smallint,
    @in_birth_month smallint,
    @in_birth_day smallint,
    @in_country smallint,
    @in_province smallint,
    @in_city smallint,
    @in_area smallint,
    @in_address nvarchar(50),
    @in_telephone nvarchar(20),
    @in_telephone_short nvarchar(20),
    @in_telephone2 nvarchar(20),
    @in_telephone2_short nvarchar(20),
    @in_mobile nvarchar(20),
    @in_mobile_short nvarchar(20),
    @in_mobile2 nvarchar(20),
    @in_mobile2_short nvarchar(20),
    @in_fax nvarchar(20),
    @in_qq nvarchar(50),
    @in_weixin nvarchar(50),
    @in_weibo nvarchar(50),
    @in_email nvarchar(50),
    @in_roomname nvarchar(50),
    @in_roomnumber nvarchar(50),
    @in_idnumber nvarchar(50),
    @in_carnumber nvarchar(50),
    @in_homepage nvarchar(80),
    @in_introduction nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    NICKNAME = @in_nickname,
    SIGNATURE = @in_signature,
    ACTUALNAME = @in_actualname,
    SEX = @in_sex,
    AGE = @in_age,
    BIRTH_YEAR = @in_birth_year,
    BIRTH_MONTH = @in_birth_month,
    BIRTH_DAY = @in_birth_day,
    COUNTRY = @in_country,
    PROVINCE = @in_province,
    CITY = @in_city,
    AREA = @in_area,
    ADDRESS = @in_address,
    TELEPHONE = @in_telephone,
    TELEPHONE_SHORT = @in_telephone_short,
    TELEPHONE2 = @in_telephone2,
    TELEPHONE2_SHORT = @in_telephone2_short,
    MOBILE = @in_mobile,
    MOBILE_SHORT = @in_mobile_short,
    MOBILE2 = @in_mobile2,
    MOBILE2_SHORT = @in_mobile2_short,
    FAX = @in_fax,
    QQ = @in_qq,
    WEIXIN = @in_weixin,
    WEIBO = @in_weibo,
    EMAIL = @in_email,
    ROOMNAME = @in_roomname,
    ROOMNUMBER = @in_roomnumber,
    IDNUMBER = @in_idnumber,
    CARNUMBER = @in_carnumber,
    HOMEPAGE = @in_homepage,
    INTRODUCTION = @in_introduction,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEFACEFILE */

CREATE PROCEDURE CHANGEFACEFILE 
    @in_userid int,
    @in_facefile nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    FACEFILE = @in_facefile,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEPHOTOFILE */

CREATE PROCEDURE CHANGEPHOTOFILE 
    @in_userid int,
    @in_photofile nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    PHOTOFILE = @in_photofile,
    MODIFYTIME = getdate()
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGELINKDEPART */

CREATE PROCEDURE CHANGELINKDEPART (
    @in_userid int,
    @in_linkdepartid int)
as
begin
  update USERS set 
    LINKDEPARTID = @in_linkdepartid
    where USERID = @in_userid;
end;
GO

/* CHANGEUSERPOSITION */

CREATE PROCEDURE CHANGEUSERPOSITION (
    @in_userid int,
    @in_userposition int)
as
begin
  update USERS set 
    USERPOSITION = @in_userposition
    where USERID = @in_userid;
end;
GO

/* GETUSERBYID */

CREATE PROCEDURE GETUSERBYID 
    @in_userid int
/*
returns (
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_logintimes bigint,
    @out_lastlogintime datetime,
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_lastclientipaddr nvarchar(50),
    @out_lastclientlocation nvarchar(50),

    @out_pwd nvarchar(255),
    @out_salt nvarchar(6),
    @out_token nvarchar(255),
    @out_tokenupdatetime datetime,
    @out_locked smallint,

    @out_onlyfindmebyid smallint,
    @out_joinsetting smallint,
    @out_joinquestion nvarchar(50),
    @out_joinanswer nvarchar(50),

    @out_lastrecvsysmsgs datetime,
    @out_linkdepartid int,
    @out_userposition int,

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,

    @out_modifytime datetime,
    @out_createtime datetime)
*/
as
begin
  select 
    USERID as out_userid, 
    USERNAME as out_username , 
    USERTYPE as out_usertype,
    CANFOUND as out_canfound,

    NICKNAME as out_nickname, 
    SIGNATURE as out_signature, 
    ACTUALNAME as out_actualname,
    SEX as out_sex, 
    AGE as out_age, 
    BIRTH_YEAR as out_birth_year, 
    BIRTH_MONTH as out_birth_month, 
    BIRTH_DAY as out_birth_day, 
    COUNTRY as out_country, 
    PROVINCE as out_province, 
    CITY as out_city, 
    AREA as out_area, 
    ADDRESS as out_address,
    TELEPHONE as out_telephone,
    TELEPHONE_SHORT as out_telephone_short,
    TELEPHONE2 as out_telephone2,
    TELEPHONE2_SHORT as out_telephone2_short,
    MOBILE as out_mobile,
    MOBILE_SHORT as out_mobile_short,
    MOBILE2 as out_mobile2,
    MOBILE2_SHORT as out_mobile2_short,
    FAX as out_fax,
    QQ as out_qq,
    WEIXIN as out_weixin,
    WEIBO as out_weibo,
    EMAIL as out_email,
    ROOMNAME as out_roomname,
    ROOMNUMBER as out_roomnumber,
    IDNUMBER as out_idnumber,
    CARNUMBER as out_carnumber,
    HOMEPAGE as out_homepage,
    DEPARTID as out_departid,
    DEPARTNAME as out_departname,
    JOBTITLE as out_jobtitle, 
    JOBNUMBER as out_jobnumber,
    INTRODUCTION as out_introduction,

    FACEFILE as out_facefile, 
    PHOTOFILE as out_photofile, 

    LOGINSTATUS as out_loginstatus, 
    LOGINTIMES as out_logintimes, 
    LASTLOGINTIME as out_lastlogintime,
    CLIENTIPADDR as out_clientipaddr, 
    CLIENTLOCATION as out_clientlocation, 
    LASTCLIENTIPADDR as out_lastclientipaddr, 
    LASTCLIENTLOCATION as out_lastclientlocation, 

    PWD as out_pwd, 
    SALT as out_salt, 
    TOKEN as out_token, 
    TOKENUPDATETIME as out_tokenupdatetime, 
    LOCKED as out_locked, 

    ONLYFINDMEBYID as out_onlyfindmebyid, 
    JOINSETTING as out_joinsetting, 
    JOINQUESTION as out_joinquestion, 
    JOINANSWER as out_joinanswer, 

    LASTRECVSYSMSGS as out_lastrecvsysmsgs,
    LINKDEPARTID as out_linkdepartid, 
    USERPOSITION as out_userposition,	

    SMSTOTAL as out_smstotal,
    SMSDAYLIMIT as out_smsdaylimit,
    SMSDAYSTART as out_smsdaystart,
    SMSDAYSENDED as out_smsdaysended,    
    SMSTOTALSENDED as out_smstotalsended,

    MODIFYTIME as out_modifytime,
    CREATETIME as out_createtime

    from USERS where USERID = @in_userid;
end;
GO

/* GETUSERBYNAME */

CREATE PROCEDURE GETUSERBYNAME 
    @in_username nvarchar(50)
/*
returns (
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_logintimes bigint,
    @out_lastlogintime datetime,
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_lastclientipaddr    nvarchar(50),
    @out_lastclientlocation    nvarchar(50),

    @out_pwd nvarchar(255),
    @out_salt nvarchar(6),
    @out_token nvarchar(255),
    @out_tokenupdatetime datetime,
    @out_locked smallint,

    @out_onlyfindmebyid smallint,
    @out_joinsetting smallint,
    @out_joinquestion nvarchar(50),
    @out_joinanswer nvarchar(50),

    @out_lastrecvsysmsgs datetime,
    @out_linkdepartid int,
    @out_userposition int,

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,

    @out_modifytime datetime,
    @out_createtime datetime)
*/
as
begin
  select 
    USERID as out_userid, 
    USERNAME as out_username, 
    USERTYPE as out_usertype,
    CANFOUND as out_canfound,

    NICKNAME as out_nickname, 
    SIGNATURE as out_signature, 
    ACTUALNAME as out_actualname,
    SEX as out_sex, 
    AGE as out_age, 
    BIRTH_YEAR as out_birth_year, 
    BIRTH_MONTH as out_birth_month, 
    BIRTH_DAY as out_birth_day, 
    COUNTRY as out_country, 
    PROVINCE as out_province, 
    CITY as out_city, 
    AREA as out_area, 
    ADDRESS as out_address,
    TELEPHONE as out_telephone,
    TELEPHONE_SHORT as out_telephone_short,
    TELEPHONE2 as out_telephone2,
    TELEPHONE2_SHORT as out_telephone2_short,
    MOBILE as out_mobile,
    MOBILE_SHORT as out_mobile_short,
    MOBILE2 as out_mobile2,
    MOBILE2_SHORT as out_mobile2_short,
    FAX as out_fax,
    QQ as out_qq,
    WEIXIN as out_weixin,
    WEIBO as out_weibo,
    EMAIL as out_email,
    ROOMNAME as out_roomname,
    ROOMNUMBER as out_roomnumber,
    IDNUMBER as out_idnumber,
    CARNUMBER as out_carnumber,
    HOMEPAGE as out_homepage,
    DEPARTID as out_departid,
    DEPARTNAME as out_departname,
    JOBTITLE as out_jobtitle, 
    JOBNUMBER as out_jobnumber,
    INTRODUCTION as out_introduction,

    FACEFILE as out_facefile, 
    PHOTOFILE as out_photofile, 

    LOGINSTATUS as out_loginstatus, 
    LOGINTIMES as out_logintimes, 
    LASTLOGINTIME as out_lastlogintime,
    CLIENTIPADDR as out_clientipaddr, 
    CLIENTLOCATION as out_clientlocation, 
    LASTCLIENTIPADDR as out_lastclientipaddr, 
    LASTCLIENTLOCATION as out_lastclientlocation, 

    PWD as out_pwd, 
    SALT as out_salt, 
    TOKEN as out_token, 
    TOKENUPDATETIME as out_tokenupdatetime, 
    LOCKED as out_locked, 

    ONLYFINDMEBYID as out_onlyfindmebyid, 
    JOINSETTING as out_joinsetting, 
    JOINQUESTION as out_joinquestion, 
    JOINANSWER as out_joinanswer, 

    LASTRECVSYSMSGS as out_lastrecvsysmsgs,
    LINKDEPARTID as out_linkdepartid, 
    USERPOSITION as out_userposition,	

    SMSTOTAL as out_smstotal,
    SMSDAYLIMIT as out_smsdaylimit,
    SMSDAYSTART as out_smsdaystart,
    SMSDAYSENDED as out_smsdaysended,    
    SMSTOTALSENDED as out_smstotalsended,

    MODIFYTIME as out_modifytime,
    CREATETIME as out_createtime

    from USERS where USERNAME = @in_username;
end;
GO

/* GETUSERBYTOKEN */

CREATE PROCEDURE GETUSERBYTOKEN 
    @in_token nvarchar(255)
/*
returns (
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_logintimes bigint,
    @out_lastlogintime datetime,
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_lastclientipaddr    nvarchar(50),
    @out_lastclientlocation    nvarchar(50),

    @out_pwd nvarchar(255),
    @out_salt nvarchar(6),
    @out_token nvarchar(255),
    @out_tokenupdatetime datetime,
    @out_locked smallint,

    @out_onlyfindmebyid smallint,
    @out_joinsetting smallint,
    @out_joinquestion nvarchar(50),
    @out_joinanswer nvarchar(50),

    @out_lastrecvsysmsgs datetime,
    @out_linkdepartid int,
    @out_userposition int,

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,

    @out_modifytime datetime,
    @out_createtime datetime)
*/
as
begin
  select 
    USERID as out_userid, 
    USERNAME as out_username , 
    USERTYPE as out_usertype,
    CANFOUND as out_canfound,

    NICKNAME as out_nickname, 
    SIGNATURE as out_signature, 
    ACTUALNAME as out_actualname,
    SEX as out_sex, 
    AGE as out_age, 
    BIRTH_YEAR as out_birth_year, 
    BIRTH_MONTH as out_birth_month, 
    BIRTH_DAY as out_birth_day, 
    COUNTRY as out_country, 
    PROVINCE as out_province, 
    CITY as out_city, 
    AREA as out_area, 
    ADDRESS as out_address,
    TELEPHONE as out_telephone,
    TELEPHONE_SHORT as out_telephone_short,
    TELEPHONE2 as out_telephone2,
    TELEPHONE2_SHORT as out_telephone2_short,
    MOBILE as out_mobile,
    MOBILE_SHORT as out_mobile_short,
    MOBILE2 as out_mobile2,
    MOBILE2_SHORT as out_mobile2_short,
    FAX as out_fax,
    QQ as out_qq,
    WEIXIN as out_weixin,
    WEIBO as out_weibo,
    EMAIL as out_email,
    ROOMNAME as out_roomname,
    ROOMNUMBER as out_roomnumber,
    IDNUMBER as out_idnumber,
    CARNUMBER as out_carnumber,
    HOMEPAGE as out_homepage,
    DEPARTID as out_departid,
    DEPARTNAME as out_departname,
    JOBTITLE as out_jobtitle, 
    JOBNUMBER as out_jobnumber,
    INTRODUCTION as out_introduction,

    FACEFILE as out_facefile, 
    PHOTOFILE as out_photofile, 

    LOGINSTATUS as out_loginstatus, 
    LOGINTIMES as out_logintimes, 
    LASTLOGINTIME as out_lastlogintime,
    CLIENTIPADDR as out_clientipaddr, 
    CLIENTLOCATION as out_clientlocation, 
    LASTCLIENTIPADDR as out_lastclientipaddr, 
    LASTCLIENTLOCATION as out_lastclientlocation, 

    PWD as out_pwd, 
    SALT as out_salt, 
    TOKEN as out_token, 
    TOKENUPDATETIME as out_tokenupdatetime, 
    LOCKED as out_locked, 

    ONLYFINDMEBYID as out_onlyfindmebyid, 
    JOINSETTING as out_joinsetting, 
    JOINQUESTION as out_joinquestion, 
    JOINANSWER as out_joinanswer, 

    LASTRECVSYSMSGS as out_lastrecvsysmsgs,
    LINKDEPARTID as out_linkdepartid, 
    USERPOSITION as out_userposition,	

    SMSTOTAL as out_smstotal,
    SMSDAYLIMIT as out_smsdaylimit,
    SMSDAYSTART as out_smsdaystart,
    SMSDAYSENDED as out_smsdaysended,    
    SMSTOTALSENDED as out_smstotalsended,

    MODIFYTIME as out_modifytime,
    CREATETIME as out_createtime

    from USERS where TOKEN = @in_token;
end;
GO

/* LOCKUSER */

CREATE PROCEDURE LOCKUSER 
    @in_userid int,
    @in_value smallint
as
begin
  SET NOCOUNT ON;

  update USERS set 
    LOCKED = @in_value
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* LOGIN */

CREATE PROCEDURE LOGIN 
    @in_logintype int,
    @in_loginname nvarchar(50),
    @in_pwd nvarchar(255),
    @in_newtoken nvarchar(255),
    @in_loginstatus int,
    @in_clientipaddr nvarchar(50),
    @in_clientlocation nvarchar(50)
/*
returns (
    @out_errorcode int,
    @out_userid int,
    @out_token nvarchar(255))
*/
as 
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;
  declare @out_userid int;
  declare @out_token nvarchar(255);

  declare @var_pwd nvarchar(255);
  declare @var_tokenupdatetime datetime;
  declare @var_lock smallint;

  set @out_errorcode = 0;
  set @out_userid = -1;
  set @out_token = '';

  set @var_pwd = '';
  set @var_lock = 0;

  if ( (RTRIM(LTRIM(@in_loginname)) = '') or
    ((@in_logintype != 2) and (RTRIM(LTRIM(@in_newtoken)) = '')) )
  begin
    set @out_errorcode = 1; /*--invalid params--*/
    goto result_label;
  end

  if (@in_logintype = 0)  /*----login by @out_userid----*/
  begin
    select @out_userid = USERID, @var_pwd = PWD, @out_token = TOKEN, 
    @var_tokenupdatetime = TOKENUPDATETIME, @var_lock = LOCKED from USERS 
    where USERID = CAST(RTRIM(LTRIM(@in_loginname)) as int);
  end
  else if (@in_logintype = 1)  /*----login by username----*/
  begin
    select @out_userid = USERID, @var_pwd = PWD, @out_token = TOKEN, 
    @var_tokenupdatetime = TOKENUPDATETIME, @var_lock = LOCKED from USERS 
    where USERNAME = RTRIM(LTRIM(@in_loginname));
  end
  else if (@in_logintype = 2)  /*----login by token----*/
  begin
    select @out_userid = USERID, @var_pwd = PWD, @out_token = TOKEN, 
    @var_tokenupdatetime = TOKENUPDATETIME, @var_lock = LOCKED from USERS 
    where TOKEN = RTRIM(LTRIM(@in_loginname));
  end
  
  if (@out_userid = -1) 
  begin
    set @out_errorcode = 2; /*--not find user--*/
    goto result_label;
  end

  if (@var_lock != 0) 
  begin
    set @out_errorcode = 3; /*--user lock--*/
    goto result_label;
  end

  if (@in_logintype != 2)  /*---login by userid,username---*/
  begin
    if ( (RTRIM(LTRIM(@in_pwd)) != '') and (@var_pwd != RTRIM(LTRIM(@in_pwd))) ) 
    begin
      set @out_errorcode = 4; /*--password error--*/
      goto result_label;
    end
    
    /*---update token---*/
    if ( (@out_token != '') and not (@var_tokenupdatetime is null) and 
      (dateadd(minute, 5, @var_tokenupdatetime) >= getdate()) ) 
    begin
      update USERS set 
        TOKENUPDATETIME = getdate()
        where USERID = @out_userid;
    end
    else
    begin
      update USERS set 
        TOKEN = RTRIM(LTRIM(@in_newtoken)),
        TOKENUPDATETIME = getdate()
        where USERID = @out_userid;
    end
  end
  else /*---login by token---*/
  begin
    if (@out_token != RTRIM(LTRIM(@in_loginname))) 
    begin
      set @out_errorcode = 5; /*--token error--*/
      goto result_label;
    end

    if ( not (@var_tokenupdatetime is null) and 
      (dateadd(minute, 5, @var_tokenupdatetime) >= getdate()) ) 
    begin
      set @out_errorcode = 6; /*--token timeout--*/
      goto result_label;
    end

    update USERS set 
      TOKENUPDATETIME = getdate()
      where USERID = @out_userid;
  end

  update USERS set 
    LASTCLIENTIPADDR = CLIENTIPADDR,
    LASTCLIENTLOCATION = CLIENTLOCATION
    where USERID = @out_userid;

  update USERS set 
    LOGINSTATUS = @in_loginstatus, 
    LOGINTIMES = (LOGINTIMES + 1),
    LASTLOGINTIME = getdate(),
    CLIENTIPADDR = @in_clientipaddr,
    CLIENTLOCATION = @in_clientlocation
    where USERID = @out_userid;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, @out_userid as out_userid, @out_token as out_token;
  return;
end;
GO

/* LOGOUT */

CREATE PROCEDURE LOGOUT 
    @in_userid int
as 
begin
  SET NOCOUNT ON;

  update USERS set LOGINSTATUS = 0, TOKEN = '' where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* REMOVEUSER */

CREATE PROCEDURE REMOVEUSER 
    @in_userid int
as
begin
  SET NOCOUNT ON;

  declare @var_groupid int;
  declare @var_roomid int;

  delete from OFFLINEFILES where (SENDER_USERID = @in_userid) or (RECEIVER_USERID = @in_userid);
  delete from OFFLINEMESSAGES where USERID = @in_userid;
  delete from OFFLINESUPEROBJECTS where FROMUSERID = @in_userid;
  delete from OFFGROUPMSGS where SENDER_USERID = @in_userid;
  delete from OFFGROUPSUPEROBJS where FROMUSERID = @in_userid;
  delete from OFFROOMMSGS where SENDER_USERID = @in_userid;
  delete from OFFROOMSUPEROBJS where FROMUSERID = @in_userid;

  delete from JOINREQUESTS where USERID = @in_userid;
  delete from JOINRESPONSES where USERID = @in_userid;
  delete from JOINGROUPREQUESTS where SENDER = @in_userid;
  delete from JOINGROUPRESPONSES where USERID = @in_userid;
  delete from JOINROOMREQUESTS where SENDER = @in_userid;
  delete from JOINROOMRESPONSES where USERID = @in_userid;

  delete from CONTACTGROUPS where USERID = @in_userid;
  delete from CONTACTS where (USERID = @in_userid) or (CONTACTID = @in_userid);

  delete from GROUPMEMBERS where MEMBERID = @in_userid;
  if (exists(select * from GROUPS where OWNERID = @in_userid)) 
  begin
	  declare cur_removefromgroup CURSOR for select GROUPID from GROUPS where OWNERID = @in_userid;
          open cur_removefromgroup;
	  fetch next from cur_removefromgroup into @var_groupid;
	  while (@@FETCH_STATUS = 0)
	  begin
	    delete from GROUPMEMBERS where GROUPID = @var_groupid;
	    delete from GROUPSHAREFILES where GROUPID = @var_groupid;
            delete from OFFGROUPMSGS where GROUPID = @var_groupid;      
            delete from OFFGROUPSUPEROBJS where GROUPID = @var_groupid;    

	    fetch next from cur_removefromgroup into @var_groupid;
	  end;
          close cur_removefromgroup;
	  deallocate cur_removefromgroup;
	  delete from GROUPS where OWNERID = @in_userid;
  end;

  delete from ROOMMEMBERS where MEMBERID = @in_userid;
  if (exists(select * from ROOMS where OWNERID = @in_userid)) 
  begin
	  declare cur_removefromroom CURSOR for select ROOMID from ROOMS where OWNERID = @in_userid;
          open cur_removefromroom;
	  fetch next from cur_removefromroom into @var_roomid;
	  while (@@FETCH_STATUS = 0)
	  begin
	    delete from ROOMMEMBERS where ROOMID = @var_roomid;
	    delete from ROOMSHAREFILES where ROOMID = @var_roomid;
            delete from OFFROOMMSGS where ROOMID = @var_roomid;      
            delete from OFFROOMSUPEROBJS where ROOMID = @var_roomid;    

	    fetch next from cur_removefromroom into @var_roomid;
	  end;
          close cur_removefromroom;
	  deallocate cur_removefromroom;
	  delete from ROOMS where OWNERID = @in_userid;
  end;

  delete from WEBVISITORS where USERID = @in_userid;
  delete from WEBVISITORMESSAGES where USERID = @in_userid;

  delete from RECENTITEMS where USERID = @in_userid;

  delete from USERS where USERID = @in_userid;

  delete from PWDPROTECTION where USERID = @in_userid;

  update ACCOUNTS set REGISTED = 0, REGISTERTIME = null, REGISTERIPADDR = '' where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* RESETLOGINSTATUS */

CREATE PROCEDURE RESETLOGINSTATUS 
    @in_userid int
as 
begin
  SET NOCOUNT ON;

  if (@in_userid = -1) 
  begin
    update USERS set LOGINSTATUS = 0 where 1 = 1;
  end
  else
  begin
    update USERS set LOGINSTATUS = 0 where USERID = @in_userid;
  end

  SET NOCOUNT OFF;
end;
GO

/* UPDATESMSSETTING */

CREATE PROCEDURE UPDATESMSSETTING (
    @in_userid int,
    @in_smstotal int,
    @in_smsdaylimit int)
as 
begin
    update USERS set SMSTOTAL = @in_smstotal, SMSDAYLIMIT = @in_smsdaylimit where USERID = @in_userid;
end;
GO

/* UPDATESMSSENDCOUNT */

CREATE PROCEDURE UPDATESMSSENDCOUNT (
    @in_userid int,
    @in_smsdaystart datetime,
    @in_smsdaysended int,
    @in_smstotalsended int)
as 
begin
  update USERS set SMSDAYSTART = @in_smsdaystart, SMSDAYSENDED = @in_smsdaysended, 
    SMSTOTALSENDED = @in_smstotalsended where USERID = @in_userid;
end;
GO

/* UPDATEUSERJOINSETTING */

CREATE PROCEDURE UPDATEUSERJOINSETTING 
    @in_userid int,
    @in_onlyfindmebyid smallint,
    @in_joinsetting smallint,
    @in_joinquestion nvarchar(50),
    @in_joinanswer nvarchar(50)
as
begin
  SET NOCOUNT ON;

  update USERS set 
    ONLYFINDMEBYID = @in_onlyfindmebyid,
    JOINSETTING = @in_joinsetting,
    JOINQUESTION = @in_joinquestion,
    JOINANSWER = @in_joinanswer
    where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETUSERJOINSETTING */

CREATE PROCEDURE GETUSERJOINSETTING 
    @in_userid int
/*
returns (
    @out_joinsetting smallint,
    @out_joinquestion nvarchar(50))
*/
as
begin
  select JOINSETTING as out_joinsetting, JOINQUESTION as out_joinquestion
    from USERS where USERID = @in_userid;
end;
GO

/* VALIDATEUSERJOINQUESTION */

CREATE PROCEDURE VALIDATEUSERJOINQUESTION 
    @in_userid int,
    @in_joinsetting smallint,
    @in_joinquestion nvarchar(50),
    @in_joinanswer nvarchar(50)
/*
returns (
    @out_errorcode int)
*/
as
begin
  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (not exists(select * from USERS where (USERID = @in_userid) and 
	(JOINSETTING = @in_joinsetting) and (JOINQUESTION = @in_joinquestion) and
	(JOINANSWER = @in_joinanswer))) 
  begin
    set @out_errorcode = 1;
  end

  select @out_errorcode as out_errorcode;
end;
GO

/* GETPWDPROTECTION */

CREATE PROCEDURE GETPWDPROTECTION 
    @in_userid int
/*
returns (
    @out_question1 nvarchar(50),
    @out_question2 nvarchar(50),
    @out_question3 nvarchar(50))
*/
as
begin
  select QUESTION1 as out_question1, QUESTION2 as out_question2, QUESTION3 as out_question3 
        from PWDPROTECTION where USERID = @in_userid;
end;
GO

/* SETPWDPROTECTION */

CREATE PROCEDURE SETPWDPROTECTION 
    @in_userid int,
    @in_question1 nvarchar(50),
    @in_answer1 nvarchar(50),
    @in_question2 nvarchar(50),
    @in_answer2 nvarchar(50),
    @in_question3 nvarchar(50),
    @in_answer3 nvarchar(50)
as
begin
  SET NOCOUNT ON;

  if (not exists(select * from PWDPROTECTION where USERID = @in_userid)) 
  begin
    insert into PWDPROTECTION(
      USERID,
      QUESTION1,
      ANSWER1,
      QUESTION2,
      ANSWER2,
      QUESTION3,
      ANSWER3,
      LASTMODIFYTIME,
      CREATETIME)
    values(
      @in_userid,
      @in_question1,
      @in_answer1,
      @in_question2,
      @in_answer2,
      @in_question3,
      @in_answer3,
      getdate(),
      getdate());
  end
  else
  begin
    update PWDPROTECTION set 
      QUESTION1 = @in_question1,
      ANSWER1 = @in_answer1,
      QUESTION2 = @in_question2,
      ANSWER2 = @in_answer2,
      QUESTION3 = @in_question3,
      ANSWER3 = @in_answer3,
      LASTMODIFYTIME = getdate()
      where USERID = @in_userid;
  end

  SET NOCOUNT OFF;
end;
GO

/* VALIDATEPWDPROTECTION */

CREATE PROCEDURE VALIDATEPWDPROTECTION 
    @in_userid int,
    @in_answer1 nvarchar(50),
    @in_answer2 nvarchar(50),
    @in_answer3 nvarchar(50)
/*
returns (
    @out_errorcode int)
*/
as
begin
  declare @out_errorcode int;

  if (not exists(select * from PWDPROTECTION where (USERID = @in_userid) and 
	(ANSWER1 = @in_answer1) and (ANSWER2 = @in_answer2) and (ANSWER3 = @in_answer3) )) 
  begin
    set @out_errorcode = 1;
  end
  else
  begin
    set @out_errorcode = 0;
  end;

  select @out_errorcode as out_errorcode;
end;
GO

/* SEARCHUSERS */

CREATE PROCEDURE SEARCHUSERS 
    @in_userid int,
    @in_param_userid int,
    @in_param_username nvarchar(50),
    @in_param_nickname nvarchar(20),
    @in_param_actualname nvarchar(20),
    @in_param_departname nvarchar(50),
    @in_param_jobnumber nvarchar(20),
    @in_param_country smallint,
    @in_param_province smallint,
    @in_param_city smallint,
    @in_param_area smallint,
    @in_param_sex smallint,
    @in_param_age smallint,
    @in_param_online smallint,
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint)
*/
as
begin
  declare @var_usertype smallint;
  declare @var_canfound smallint;
  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(1024);
  declare @var_where_sql_temp nvarchar(200);
  declare @var_paging_sql nvarchar(4000);

  set @var_usertype = null;
  set @var_canfound = null;

  select @var_usertype = USERTYPE, 
    @var_canfound = CANFOUND 
    from USERS where USERID = @in_userid;

  if ((@var_usertype is null) or 
    (@var_canfound is null)) 
  begin
    return;
  end;

  if (@var_usertype = 0)  /*PublicUsers*/
  begin
      set @var_where_sql = '( (USERTYPE = 0)';

      set @var_where_sql = @var_where_sql + ' or ((USERTYPE = 1) and (CANFOUND = 1)) )';
  end
  else if (@var_usertype = 1)  /*EnterpriseUsers*/
  begin
      set @var_where_sql = '( (USERTYPE = 1)';

      if (@var_canfound = 1) 
      begin
        set @var_where_sql = @var_where_sql + ' or (USERTYPE = 0)';
      end;

      set @var_where_sql = @var_where_sql + ')';
  end
  else
  begin
    return;
  end;

  if (@in_param_userid > 0) 
  begin
    set @var_where_sql_temp = '(USERID = ' + cast(@in_param_userid as nvarchar(20)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end
  else
  begin
    set @var_where_sql_temp = '(ONLYFINDMEBYID = 0)';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_username <> '') 
  begin
    set @var_where_sql_temp = '(USERNAME = ''' + @in_param_username + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_nickname <> '') 
  begin
    set @var_where_sql_temp = '(NICKNAME = ''' + @in_param_nickname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_actualname <> '') 
  begin
    set @var_where_sql_temp = '(ACTUALNAME = ''' + @in_param_actualname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_departname <> '') 
  begin
    set @var_where_sql_temp = '(DEPARTNAME = ''' + @in_param_departname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_jobnumber <> '') 
  begin
    set @var_where_sql_temp = '(JOBNUMBER = ''' + @in_param_jobnumber + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_country <> -1) 
  begin
    set @var_where_sql_temp = '(COUNTRY = ' + cast(@in_param_country as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_province <> -1) 
  begin
    set @var_where_sql_temp = '(PROVINCE = ' + cast(@in_param_province as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_city <> -1) 
  begin
    set @var_where_sql_temp = '(CITY = ' + cast(@in_param_city as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_area <> -1) 
  begin
    set @var_where_sql_temp = '(AREA = ' + cast(@in_param_area as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_sex <> -1) 
  begin
    set @var_where_sql_temp = '(SEX = ' + cast(@in_param_sex as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_age <> -1) 
  begin
    if (@in_param_age = 1) 
      set @var_where_sql_temp = '( (AGE >= 0) and (AGE <= 18))';
	else if (@in_param_age = 2) 
      set @var_where_sql_temp = '( (AGE >= 19) and (AGE <= 24))';
	else if (@in_param_age = 3) 
      set @var_where_sql_temp = '( (AGE >= 25) and (AGE <= 30))';
	else if (@in_param_age = 4) 
      set @var_where_sql_temp = '( (AGE >= 31) and (AGE <= 40))';
	else if (@in_param_age = 5) 
      set @var_where_sql_temp = '( (AGE >= 41) and (AGE <= 50))';
	else if (@in_param_age = 6) 
      set @var_where_sql_temp = '(AGE >= 51)';
	else
	  set @var_where_sql_temp = '';

    if (@var_where_sql_temp <> '') 
	  set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_online = 1) 
  begin
    set @var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 5))';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  set @var_where_sql = @var_where_sql + ' order by USERID ASC';

  set @var_fields_sql = 
	'USERID as out_userid, USERNAME as out_USERNAME, USERTYPE as out_usertype,' +
	'NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age,' +
	'BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, ' +
	'COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area,' +
	'ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, ' +
	'TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, ' +
	'MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, ' +
	'WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, ' +
	'CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, ' +
	'JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile,' +
	' PHOTOFILE as out_photofile, LOGINSTATUS as out_loginstatus';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' +
          ' (USERID > (select max(USERID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' USERID from USERS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  exec(@var_paging_sql);
end;
GO

/* SEARCHPUBLICUSERS */

CREATE PROCEDURE SEARCHPUBLICUSERS 
    @in_param_beginuserid int,
    @in_param_enduserid int,
    @in_param_username nvarchar(50),
    @in_param_nickname nvarchar(20),
    @in_param_actualname nvarchar(20),
    @in_param_sex smallint,
    @in_param_age smallint,
    @in_param_online smallint,
    @in_param_locked smallint,
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_allusercount int,
    @out_onlineusercount int,
    @out_maxpageindex int,
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_logintimes bigint,
    @out_lastlogintime datetime,
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_lastclientipaddr nvarchar(50),
    @out_lastclientlocation nvarchar(50),

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,

    @out_locked smallint,
    @out_createtime datetime)
*/
as
begin
  declare @var_allusercount int;
  declare @var_onlineusercount int;
  declare @var_maxpageindex int;

  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(1024);
  declare @var_where_sql_temp nvarchar(200);
  declare @var_paging_sql nvarchar(4000);
  declare @var_allusercount_sql nvarchar(1000);
  declare @var_onlineusercount_sql nvarchar(1000);

  set @var_where_sql = '(USERTYPE = 0)';

  if (@in_param_beginuserid > 0) 
  begin
    set @var_where_sql_temp = '(USERID >= ' + cast(@in_param_beginuserid as nvarchar(20)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if ((@in_param_enduserid > 0) and (@in_param_enduserid >= @in_param_beginuserid)) 
  begin
    set @var_where_sql_temp = '(USERID <= ' + cast(@in_param_beginuserid as nvarchar(20)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_username <> '') 
  begin
    set @var_where_sql_temp = '(USERNAME = ''' + @in_param_username + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_nickname <> '') 
  begin
    set @var_where_sql_temp = '(NICKNAME = ''' + @in_param_nickname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_actualname <> '') 
  begin
    set @var_where_sql_temp = '(ACTUALNAME = ''' + @in_param_actualname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_sex <> -1) 
  begin
    set @var_where_sql_temp = '(SEX = ' + cast(@in_param_sex as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_age <> -1) 
  begin
    if (@in_param_age = 1) 
      set @var_where_sql_temp = '( (AGE >= 0) and (AGE <= 18))';
	else if (@in_param_age = 2) 
      set @var_where_sql_temp = '( (AGE >= 19) and (AGE <= 24))';
	else if (@in_param_age = 3) 
      set @var_where_sql_temp = '( (AGE >= 25) and (AGE <= 30))';
	else if (@in_param_age = 4) 
      set @var_where_sql_temp = '( (AGE >= 31) and (AGE <= 40))';
	else if (@in_param_age = 5) 
      set @var_where_sql_temp = '( (AGE >= 41) and (AGE <= 50))';
	else if (@in_param_age = 6) 
      set @var_where_sql_temp = '(AGE >= 51)';
	else
	  set @var_where_sql_temp = '';

    if (@var_where_sql_temp <> '') 
	  set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_online = 1) 
  begin
    set @var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 6))';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  if (@in_param_locked = 1) 
  begin
    set @var_where_sql_temp = '(LOCKED = 1)';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end;

  set @var_allusercount_sql = 'select @var_allusercount = count(*) from USERS where ' + @var_where_sql;
  exec SP_EXECUTESQL @var_allusercount_sql, N'@var_allusercount int output', @var_allusercount output;

  set @var_onlineusercount_sql = 'select @var_onlineusercount = count(*) from USERS where ' + @var_where_sql + ' and (LOGINSTATUS <> 0)';
  exec SP_EXECUTESQL @var_onlineusercount_sql, N'@var_onlineusercount int output', @var_onlineusercount output;

  set @var_maxpageindex = @var_allusercount / @in_pagesize;
  if ((@var_allusercount % @in_pagesize) > 0) 
  begin
    set @var_maxpageindex = @var_maxpageindex + 1;
  end;

  if (@var_maxpageindex <= 0) set @var_maxpageindex = 1;

  if ((@in_pageindex > @var_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @var_maxpageindex;
  end;

  set @var_where_sql = @var_where_sql + ' order by CREATETIME DESC';

  set @var_fields_sql = 
      str(@var_allusercount) + ' as out_allusercount, ' +
      str(@var_onlineusercount) + ' as out_onlineusercount, ' +
      str(@var_maxpageindex) + ' as out_maxpageindex, ' +
	'USERID as out_userid, USERNAME as out_USERNAME, USERTYPE as out_usertype, CANFOUND as out_canfound,' +
	'NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age,' +
	'BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, ' +
	'COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area,' +
	'ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, ' +
	'TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, ' +
	'MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, ' +
	'WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, ' +
	'CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, ' +
	'JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, PHOTOFILE as out_photofile,' +
    ' LOGINSTATUS as out_loginstatus, LOGINTIMES as out_logintimes, LASTLOGINTIME as out_lastlogintime,' +
    ' CLIENTIPADDR as out_clientipaddr, CLIENTLOCATION as out_clientlocation,LASTCLIENTIPADDR as out_lastclientipaddr,' +
    ' LASTCLIENTLOCATION as out_lastclientlocation, SMSTOTAL as out_smstotal, SMSDAYLIMIT as out_smsdaylimit, ' +
    ' SMSDAYSTART as out_smsdaystart, SMSDAYSENDED as out_smsdaysended, SMSTOTALSENDED as out_smstotalsended, ' +
    ' LOCKED as out_locked, CREATETIME as out_createtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' +
          ' (USERID < (select min(USERID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' USERID from USERS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  exec(@var_paging_sql);
end;
GO

/* SEARCHENTERPRISEUSERS */

CREATE PROCEDURE SEARCHENTERPRISEUSERS 
    @in_param_beginuserid int,
    @in_param_enduserid int,
    @in_param_username nvarchar(50),
    @in_param_nickname nvarchar(20),
    @in_param_actualname nvarchar(20),
    @in_param_departname nvarchar(50),
    @in_param_jobnumber nvarchar(20),
    @in_param_sex smallint,
    @in_param_age smallint,
    @in_param_online smallint,
    @in_param_locked smallint,
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_allusercount int,
    @out_onlineusercount int,
    @out_maxpageindex int,
    @out_userid int,
    @out_username nvarchar(50),
    @out_usertype smallint,
    @out_canfound smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),

    @out_loginstatus smallint,
    @out_logintimes bigint,
    @out_lastlogintime datetime,
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_lastclientipaddr nvarchar(50),
    @out_lastclientlocation nvarchar(50),

    @out_smstotal int,
    @out_smsdaylimit int,
    @out_smsdaystart datetime,
    @out_smsdaysended int,    
    @out_smstotalsended int,

    @out_locked smallint,
    @out_createtime datetime)
*/
as
begin
  declare @var_allusercount int;
  declare @var_onlineusercount int;
  declare @var_maxpageindex int;

  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(1024);
  declare @var_where_sql_temp nvarchar(200);
  declare @var_paging_sql nvarchar(4000);
  declare @var_allusercount_sql nvarchar(1000);
  declare @var_onlineusercount_sql nvarchar(1000);

  set @var_where_sql = '(USERTYPE = 1)';

  if (@in_param_beginuserid > 0) 
  begin
    set @var_where_sql_temp = '(USERID >= ' + cast(@in_param_beginuserid as nvarchar(20)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if ((@in_param_enduserid > 0) and (@in_param_enduserid >= @in_param_beginuserid)) 
  begin
    set @var_where_sql_temp = '(USERID <= ' + cast(@in_param_beginuserid as nvarchar(20)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_username <> '') 
  begin
    set @var_where_sql_temp = '(USERNAME = ''' + @in_param_username + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_nickname <> '') 
  begin
    set @var_where_sql_temp = '(NICKNAME = ''' + @in_param_nickname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_actualname <> '') 
  begin
    set @var_where_sql_temp = '(ACTUALNAME = ''' + @in_param_actualname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_departname <> '') 
  begin
    set @var_where_sql_temp = '(DEPARTNAME = ''' + @in_param_departname + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_jobnumber <> '') 
  begin
    set @var_where_sql_temp = '(JOBNUMBER = ''' + @in_param_jobnumber + ''')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_sex <> -1) 
  begin
    set @var_where_sql_temp = '(SEX = ' + cast(@in_param_sex as nvarchar(10)) + ')';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_age <> -1) 
  begin
    if (@in_param_age = 1) 
      set @var_where_sql_temp = '( (AGE >= 0) and (AGE <= 18))';
	else if (@in_param_age = 2) 
      set @var_where_sql_temp = '( (AGE >= 19) and (AGE <= 24))';
	else if (@in_param_age = 3) 
      set @var_where_sql_temp = '( (AGE >= 25) and (AGE <= 30))';
	else if (@in_param_age = 4) 
      set @var_where_sql_temp = '( (AGE >= 31) and (AGE <= 40))';
	else if (@in_param_age = 5) 
      set @var_where_sql_temp = '( (AGE >= 41) and (AGE <= 50))';
	else if (@in_param_age = 6) 
      set @var_where_sql_temp = '(AGE >= 51)';
	else
	  set @var_where_sql_temp = '';

    if (@var_where_sql_temp <> '') 
	  set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_online = 1) 
  begin
    set @var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 5))';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  if (@in_param_locked = 1) 
  begin
    set @var_where_sql_temp = '(LOCKED = 1)';
    set @var_where_sql = @var_where_sql + ' and ' + @var_where_sql_temp;
  end

  set @var_allusercount_sql = 'select @var_allusercount = count(*) from USERS where ' + @var_where_sql;
  exec SP_EXECUTESQL @var_allusercount_sql, N'@var_allusercount int output', @var_allusercount output;

  set @var_onlineusercount_sql = 'select @var_onlineusercount = count(*) from USERS where ' + @var_where_sql + ' and (LOGINSTATUS <> 0)';
  exec SP_EXECUTESQL @var_onlineusercount_sql, N'@var_onlineusercount int output', @var_onlineusercount output;

  set @var_maxpageindex = @var_allusercount / @in_pagesize;
  if ((@var_allusercount % @in_pagesize) > 0) 
  begin
    set @var_maxpageindex = @var_maxpageindex + 1;
  end

  if (@var_maxpageindex <= 0) set @var_maxpageindex = 1;

  if ((@in_pageindex > @var_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @var_maxpageindex;
  end;

  set @var_where_sql = @var_where_sql + ' order by CREATETIME DESC';

  set @var_fields_sql = 
      str(@var_allusercount) + ' as out_allusercount, ' +
      str(@var_onlineusercount) + ' as out_onlineusercount, ' +
      str(@var_maxpageindex) + ' as out_maxpageindex, ' +
    'USERID as out_userid, USERNAME as out_USERNAME, USERTYPE as out_usertype, CANFOUND as out_canfound,' +
	'NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age,' +
	'BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, ' +
	'COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area,' +
	'ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, ' +
	'TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, ' +
	'MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, ' +
	'WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, ' +
	'CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, ' +
	'JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, PHOTOFILE as out_photofile,' +
    ' LOGINSTATUS as out_loginstatus, LOGINTIMES as out_logintimes, LASTLOGINTIME as out_lastlogintime,' +
    ' CLIENTIPADDR as out_clientipaddr, CLIENTLOCATION as out_clientlocation,LASTCLIENTIPADDR as out_lastclientipaddr,' +
    ' LASTCLIENTLOCATION as out_lastclientlocation, SMSTOTAL as out_smstotal, SMSDAYLIMIT as out_smsdaylimit, ' +
    ' SMSDAYSTART as out_smsdaystart, SMSDAYSENDED as out_smsdaysended, SMSTOTALSENDED as out_smstotalsended, ' +
    ' LOCKED as out_locked, CREATETIME as out_createtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from USERS where ' +
          ' (USERID < (select min(USERID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' USERID from USERS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;
  
  exec(@var_paging_sql);
end;
GO

/* COUNTPUBLICUSERS */

CREATE PROCEDURE COUNTPUBLICUSERS 
/*
returns (
    @out_count int)
*/
as
begin
  select count(*) as out_count from USERS where USERTYPE = 0;
end;
GO

/* COUNTENTERPRISEUSERS */

CREATE PROCEDURE COUNTENTERPRISEUSERS 
/*
returns (
    @out_count int)
*/
as
begin
  select count(*) as out_count from USERS where USERTYPE = 1;
end;
GO

/* ADDCONTACTGROUP */

CREATE PROCEDURE ADDCONTACTGROUP 
    @in_userid int,
    @in_contactgroupname nvarchar(20)
/*
returns(
    @out_contactgroupid		int,
    @out_contactgroupname	nvarchar(20),
    @out_hidewhenonline		smallint,
    @out_onlinewhenhide		smallint,
    @out_remindmewhenlogin	smallint,
    @out_nomessage		smallint,
    @out_ordervalue		int,
    @out_createtime		datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_contactgroupid int;
  declare @var_ordervalue int;

  select @var_contactgroupid = Max(CONTACTGROUPID) from CONTACTGROUPS where USERID = @in_userid;
  select @var_ordervalue = Max(ORDERVALUE) from CONTACTGROUPS where USERID = @in_userid;

  if (@var_contactgroupid is null)  
    set @var_contactgroupid = 101;
  else
    set @var_contactgroupid = @var_contactgroupid + 1;

  if (@var_ordervalue is null)  
    set @var_ordervalue = 1;
  else
    set @var_ordervalue = @var_ordervalue + 1;

  insert into CONTACTGROUPS(
    CONTACTGROUPID,
    USERID,
    CONTACTGROUPNAME,
    HIDEWHENONLINE,
    ONLINEWHENHIDE,
    REMINDMEWHENLOGIN,
    NOMESSAGE,
    ORDERVALUE,
    CREATETIME
  ) values(
    @var_contactgroupid,
    @in_userid,
    @in_contactgroupname,
    0,
    0,
    0,
    0,
    @var_ordervalue,
    getdate()
  );

  SET NOCOUNT OFF;

  select CONTACTGROUPID as out_contactgroupid, CONTACTGROUPNAME as out_contactgroupname, 
        HIDEWHENONLINE as out_hidewhenonline, ONLINEWHENHIDE as out_onlinewhenhide, 
	REMINDMEWHENLOGIN as out_remindmewhenlogin, NOMESSAGE as out_nomessage, 
	ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
	from CONTACTGROUPS where CONTACTGROUPID = @var_contactgroupid and USERID = @in_userid;
end;
GO

/* REMOVECONTACTGROUP */

CREATE PROCEDURE REMOVECONTACTGROUP 
    @in_userid int,
    @in_contactgroupid int
as
begin
  SET NOCOUNT ON;

  update CONTACTS set CONTACTGROUPID = 0 
    where USERID = @in_userid and CONTACTGROUPID = @in_contactgroupid;

  delete from CONTACTGROUPS 
    where USERID = @in_userid and CONTACTGROUPID = @in_contactgroupid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGECONTACTGROUPNAME */

CREATE PROCEDURE CHANGECONTACTGROUPNAME 
    @in_userid int,
    @in_contactgroupid int,
    @in_contactgroupname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  update CONTACTGROUPS set CONTACTGROUPNAME = @in_contactgroupname 
    where USERID = @in_userid and CONTACTGROUPID = @in_contactgroupid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGECONTACTGROUPSETTING */

CREATE PROCEDURE CHANGECONTACTGROUPSETTING 
    @in_userid int,
    @in_contactgroupid int,
    @in_hidewhenonline		smallint,
    @in_onlinewhenhide		smallint,
    @in_remindmewhenlogin	smallint,
    @in_nomessage		smallint
as
begin
  SET NOCOUNT ON;

  update CONTACTGROUPS set HIDEWHENONLINE = @in_hidewhenonline, ONLINEWHENHIDE = @in_onlinewhenhide, 
    REMINDMEWHENLOGIN = @in_remindmewhenlogin, NOMESSAGE = @in_nomessage
    where USERID = @in_userid and CONTACTGROUPID = @in_contactgroupid;

  SET NOCOUNT OFF;
end;
GO

/* GETCONTACTGROUPS */

CREATE PROCEDURE GETCONTACTGROUPS 
    @in_userid int
/*
returns (
    @out_contactgroupid		int,
    @out_userid			int,
    @out_contactgroupname	nvarchar(20),
    @out_hidewhenonline		smallint,
    @out_onlinewhenhide		smallint,
    @out_remindmewhenlogin	smallint,
    @out_nomessage		smallint,
    @out_ordervalue		int,
    @out_createtime		datetime)
*/
as
begin
  select CONTACTGROUPID as out_contactgroupid, USERID as out_userid, CONTACTGROUPNAME as out_contactgroupname, 
    HIDEWHENONLINE as out_hidewhenonline, ONLINEWHENHIDE as out_onlinewhenhide, REMINDMEWHENLOGIN as out_remindmewhenlogin,  
    NOMESSAGE as out_nomessage, ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
    from CONTACTGROUPS where USERID = @in_userid order by ORDERVALUE ASC; 
end;
GO

/* ADDCONTACT */

CREATE PROCEDURE ADDCONTACT 
    @in_userid int,
    @in_contactgroupid int,
    @in_contactid int,
    @in_remarkname nvarchar(20)
/*
returns(
    @out_contactid		int,
    @out_contactgroupid		int,
    @out_remarkname		nvarchar(20),
    @out_hidewhenonline		smallint,
    @out_onlinewhenhide		smallint,
    @out_remindmewhenlogin	smallint,
    @out_nomessage		smallint,
    @out_jointime		datetime,
    
    @out_username nvarchar(50),
    @out_usertype smallint,
    
    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint,
    
    @out_modifytime datetime)
*/
as
begin
  SET NOCOUNT ON;

  if (not exists(select * from CONTACTS where USERID = @in_userid and CONTACTID = @in_contactid)) 
  begin
	  insert into CONTACTS(
	    CONTACTID,
	    USERID,
	    CONTACTGROUPID,
	    REMARKNAME,
	    HIDEWHENONLINE,
	    ONLINEWHENHIDE,
	    REMINDMEWHENLOGIN,
	    NOMESSAGE,
	    JOINTIME
	  ) values(
	    @in_contactid,
	    @in_userid,
	    @in_contactgroupid,
	    @in_remarkname,
	    0,
	    0,
	    0,
	    0,
	    getdate()
	  );
  end;

  SET NOCOUNT OFF;

  select CONTACTID as out_contactid, CONTACTGROUPID as out_contactgroupid, REMARKNAME as out_remarkname,
        HIDEWHENONLINE as out_hidewhenonline, ONLINEWHENHIDE as out_onlinewhenhide, REMINDMEWHENLOGIN as out_remindmewhenlogin, 
	NOMESSAGE as out_nomessage, JOINTIME as out_jointime, USERNAME as out_username, USERTYPE as out_usertype,
	NICKNAME as out_nickname, SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age, 
	BIRTH_YEAR as out_birth_year, BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, 
	COUNTRY as out_country, PROVINCE as out_province, CITY as out_city, AREA as out_area, 
	ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, 
       TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, 
       MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, 
       WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, 
       CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, 
       JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, 
	PHOTOFILE as out_photofile, LOGINSTATUS as out_loginstatus, MODIFYTIME as out_modifytime
	from CONTACTS inner join USERS on CONTACTS.CONTACTID = USERS.USERID 
	where CONTACTS.USERID = @in_userid and CONTACTS.CONTACTID = @in_contactid;
end;
GO

/* REMOVECONTACT */

CREATE PROCEDURE REMOVECONTACT 
    @in_userid int,
    @in_contactid int,
    @in_removeotherside smallint
as
begin
  SET NOCOUNT ON;

  delete from CONTACTS 
    where USERID = @in_userid and CONTACTID = @in_contactid;

  if (@in_removeotherside = 1) 
  begin
    delete from CONTACTS 
      where USERID = @in_contactid and CONTACTID = @in_userid;
  end

  SET NOCOUNT OFF;
end;
GO

/* MOVECONTACT */

CREATE PROCEDURE MOVECONTACT 
    @in_userid int,
    @in_contactid int,
    @in_contactgroupid int
as
begin
  SET NOCOUNT ON;

  update CONTACTS set CONTACTGROUPID = @in_contactgroupid
    where USERID = @in_userid and CONTACTID = @in_contactid;

  SET NOCOUNT OFF;
end;
GO

/* MOVETOBLACKLIST */

CREATE PROCEDURE MOVETOBLACKLIST 
    @in_userid int,
    @in_contactid int
as
begin
  SET NOCOUNT ON;

  update CONTACTS set CONTACTGROUPID = 2
    where USERID = @in_userid and CONTACTID = @in_contactid;

  delete from CONTACTS
    where USERID = @in_contactid and CONTACTID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGECONTACTREMARKNAME */

CREATE PROCEDURE CHANGECONTACTREMARKNAME 
    @in_userid int,
    @in_contactid int,
    @in_remarkname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  update CONTACTS set REMARKNAME = @in_remarkname 
    where USERID = @in_userid and CONTACTID = @in_contactid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGECONTACTSETTING */

CREATE PROCEDURE CHANGECONTACTSETTING 
    @in_userid int,
    @in_contactid int,
    @in_hidewhenonline		smallint,
    @in_onlinewhenhide		smallint,
    @in_remindmewhenlogin	smallint,
    @in_nomessage		smallint
as
begin
  SET NOCOUNT ON;

  update CONTACTS set HIDEWHENONLINE = @in_hidewhenonline, ONLINEWHENHIDE = @in_onlinewhenhide, 
    REMINDMEWHENLOGIN = @in_remindmewhenlogin, NOMESSAGE = @in_nomessage
    where USERID = @in_userid and CONTACTID = @in_contactid;

  SET NOCOUNT OFF;
end;
GO

/* GETCONTACTSETTINGS */

CREATE PROCEDURE GETCONTACTSETTINGS 
    @in_userid int
/*
returns(
    @out_contactid		int,
    @out_hidewhenonline		smallint,
    @out_onlinewhenhide		smallint,
    @out_remindmewhenlogin	smallint,
    @out_nomessage		smallint)
*/
as
begin
  select CONTACTID as out_contactid, HIDEWHENONLINE as out_hidewhenonline, 
    ONLINEWHENHIDE as out_onlinewhenhide, REMINDMEWHENLOGIN as out_remindmewhenlogin, 
    NOMESSAGE as out_nomessage from CONTACTS
    where USERID = @in_userid and not (HIDEWHENONLINE = 0 and 
      ONLINEWHENHIDE = 0 and REMINDMEWHENLOGIN = 0 and NOMESSAGE = 0);
end;
GO

/* GETCONTACTS */

CREATE PROCEDURE GETCONTACTS 
    @in_userid int
/*
returns(
    @out_contactid		int,
    @out_contactgroupid		int,
    @out_remarkname		nvarchar(20),
    @out_hidewhenonline		smallint,
    @out_onlinewhenhide		smallint,
    @out_remindmewhenlogin	smallint,
    @out_nomessage		smallint,
    @out_jointime		datetime,
    
    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint,
    
    @out_modifytime datetime)
*/
as
begin
  select CONTACTID as out_contactid, CONTACTGROUPID as out_contactgroupid, REMARKNAME as out_remarkname, 
        HIDEWHENONLINE as out_hidewhenonline, ONLINEWHENHIDE as out_onlinewhenhide, REMINDMEWHENLOGIN as out_remindmewhenlogin, 
	NOMESSAGE as out_nomessage, JOINTIME as out_jointime, USERNAME as out_username, USERTYPE as out_usertype, NICKNAME as out_nickname, 
	SIGNATURE as out_signature, ACTUALNAME as out_actualname, SEX as out_sex, AGE as out_age, BIRTH_YEAR as out_birth_year, 
	BIRTH_MONTH as out_birth_month, BIRTH_DAY as out_birth_day, COUNTRY as out_country, PROVINCE as out_province, 
	CITY as out_city, AREA as out_area, ADDRESS as out_address, TELEPHONE as out_telephone, TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, 
       TELEPHONE2_SHORT as out_telephone2_short, MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, 
       MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, 
       WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, 
       CARNUMBER as out_carnumber, HOMEPAGE as out_homepage, DEPARTID as out_departid, DEPARTNAME as out_departname, 
       JOBTITLE as out_jobtitle, JOBNUMBER as out_jobnumber, INTRODUCTION as out_introduction, FACEFILE as out_facefile, 
       PHOTOFILE as out_photofile, LOGINSTATUS as out_loginstatus, MODIFYTIME as out_modifytime
	from CONTACTS inner join USERS on CONTACTS.CONTACTID = USERS.USERID where CONTACTS.USERID = @in_userid;
end;
GO

/* ADDADDRLSTCATEGORY */

CREATE PROCEDURE ADDADDRLSTCATEGORY 
    @in_userid int,
    @in_categoryname nvarchar(50),
    @in_parentcategoryid int
/*
returns (
    out_categoryid int,
    out_userid int,
    out_categoryname nvarchar(50),
    out_ordervalue int,
    out_parentcategoryid int,
    out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_newcategoryid int;
  declare @var_ordervalue int;

  select @var_newcategoryid = max(CATEGORYID) from ADDRLSTCATEGORYS where 1 = 1;
  if (@var_newcategoryid is null) set @var_newcategoryid = 0;
  set @var_newcategoryid = @var_newcategoryid + 1;

  select @var_ordervalue = max(ORDERVALUE) from ADDRLSTCATEGORYS where USERID = @in_userid;
  if (@var_ordervalue is null) set @var_ordervalue = 0;
  set @var_ordervalue = @var_ordervalue + 1;

  insert into ADDRLSTCATEGORYS(
    CATEGORYID,
    USERID,
    CATEGORYNAME,
    ORDERVALUE,
    PARENTCATEGORYID,
    CREATETIME
    )
    values(
    @var_newcategoryid,
    @in_userid,
    @in_categoryname,
    @var_ordervalue,
    @in_parentcategoryid,
    getdate()
    );

  SET NOCOUNT OFF;

  select CATEGORYID as out_categoryid, USERID as out_userid, CATEGORYNAME as out_categoryname, 
       ORDERVALUE as out_ordervalue, PARENTCATEGORYID as out_parentcategoryid, CREATETIME as out_createtime
       from ADDRLSTCATEGORYS where CATEGORYID = @var_newcategoryid and USERID = @in_userid;
end;
GO

/* ADDADDRLSTCATEGORYEX */

CREATE PROCEDURE ADDADDRLSTCATEGORYEX 
    @in_userid int,
    @in_categoryid int,
    @in_categoryname nvarchar(50),
    @in_ordervalue int,
    @in_parentcategoryid int
/*
returns (
    out_errorcode int,
    out_categoryid int,
    out_userid int,
    out_categoryname nvarchar(50),
    out_ordervalue int,
    out_parentcategoryid int,
    out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (exists(select * from ADDRLSTCATEGORYS where CATEGORYID = @in_categoryid and USERID = @in_userid))
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  insert into ADDRLSTCATEGORYS(
    CATEGORYID,
    USERID,
    CATEGORYNAME,
    ORDERVALUE,
    PARENTCATEGORYID,
    CREATETIME
    )
    values(
    @in_categoryid,
    @in_userid,
    @in_categoryname,
    @in_ordervalue,
    @in_parentcategoryid,
    getdate()
    );

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, CATEGORYID as out_categoryid, USERID as out_userid, CATEGORYNAME as out_categoryname, 
       ORDERVALUE as out_ordervalue, PARENTCATEGORYID as out_parentcategoryid, CREATETIME as out_createtime
       from ADDRLSTCATEGORYS where CATEGORYID = @in_categoryid and USERID = @in_userid;
  return;
end;
GO

/* MODIFYADDRLSTCATEGORY */

CREATE PROCEDURE MODIFYADDRLSTCATEGORY 
    @in_userid int,
    @in_categoryid int,
    @in_categoryname nvarchar(50)
as
begin
  SET NOCOUNT ON;

  update ADDRLSTCATEGORYS set CATEGORYNAME = @in_categoryname where CATEGORYID = @in_categoryid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* SETADDRLSTCATEGORYORDERVALUE */

CREATE PROCEDURE SETADDRLSTCATEGORYORDERVALUE 
    @in_userid int,
    @in_categoryid int,
    @in_ordervalue int
as
begin
  SET NOCOUNT ON;

  update ADDRLSTCATEGORYS set ORDERVALUE = @in_ordervalue where CATEGORYID = @in_categoryid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETADDRLSTCATEGORY */

CREATE PROCEDURE GETADDRLSTCATEGORY 
    @in_userid int,
    @in_categoryid int
/*
returns (
    out_categoryid int,
    out_userid int,
    out_categoryname nvarchar(50),
    out_ordervalue int,
    out_parentcategoryid int,
    out_createtime datetime)
*/
as
begin
  select CATEGORYID as out_categoryid, USERID as out_userid, CATEGORYNAME as out_categoryname, 
       ORDERVALUE as out_ordervalue, PARENTCATEGORYID as out_parentcategoryid, CREATETIME as out_createtime
       from ADDRLSTCATEGORYS where CATEGORYID = @in_categoryid and USERID = @in_userid;
end;
GO

/* GETADDRLSTCATEGORYS */

CREATE PROCEDURE GETADDRLSTCATEGORYS 
    @in_userid int,
    @in_parentcategoryid int
/*
returns (
    out_categoryid int,
    out_userid int,
    out_categoryname nvarchar(50),
    out_ordervalue int,
    out_parentcategoryid int,
    out_createtime datetime)
*/
as
begin
  if (@in_parentcategoryid >= 0)
  begin
      select CATEGORYID as out_categoryid, USERID as out_userid, CATEGORYNAME as out_categoryname, 
           ORDERVALUE as out_ordervalue, PARENTCATEGORYID as out_parentcategoryid, CREATETIME as out_createtime
           from ADDRLSTCATEGORYS where PARENTCATEGORYID = @in_parentcategoryid and USERID = @in_userid
           order by PARENTCATEGORYID ASC, ORDERVALUE ASC;
  end
  else
  begin
      select CATEGORYID as out_categoryid, USERID as out_userid, CATEGORYNAME as out_categoryname, 
           ORDERVALUE as out_ordervalue, PARENTCATEGORYID as out_parentcategoryid, CREATETIME as out_createtime
           from ADDRLSTCATEGORYS where USERID = @in_userid order by PARENTCATEGORYID ASC, ORDERVALUE ASC;
  end
end;
GO

/* ADDADDRLSTITEM */

CREATE PROCEDURE ADDADDRLSTITEM 
    @in_userid int,
    @in_categoryid int,
    @in_actualname nvarchar(20),
    @in_sex smallint,
    @in_age smallint,
    @in_zipcode nvarchar(10),
    @in_address nvarchar(50),
    @in_telephone nvarchar(20),
    @in_telephone_short nvarchar(20),
    @in_telephone2 nvarchar(20),
    @in_telephone2_short nvarchar(20),
    @in_mobile nvarchar(20),
    @in_mobile_short nvarchar(20),
    @in_mobile2 nvarchar(20),
    @in_mobile2_short nvarchar(20),
    @in_fax nvarchar(20),
    @in_qq nvarchar(50),
    @in_weixin nvarchar(50),
    @in_weibo nvarchar(50),
    @in_email nvarchar(50),
    @in_roomname nvarchar(50),
    @in_roomnumber nvarchar(50),
    @in_idnumber nvarchar(50),
    @in_carnumber nvarchar(50),
    @in_description nvarchar(255)
/*
returns (
    out_itemid int,
    out_categoryid int,
    out_userid int,
    out_actualname nvarchar(20),
    out_sex smallint,
    out_age smallint,
    out_zipcode nvarchar(10),
    out_address nvarchar(50),
    out_telephone nvarchar(20),
    out_telephone_short nvarchar(20),
    out_telephone2 nvarchar(20),
    out_telephone2_short nvarchar(20),
    out_mobile nvarchar(20),
    out_mobile_short nvarchar(20),
    out_mobile2 nvarchar(20),
    out_mobile2_short nvarchar(20),
    out_fax nvarchar(20),
    out_qq nvarchar(50),
    out_weixin nvarchar(50),
    out_weibo nvarchar(50),
    out_email nvarchar(50),
    out_roomname nvarchar(50),
    out_roomnumber nvarchar(50),
    out_idnumber nvarchar(50),
    out_carnumber nvarchar(50),   
    out_description nvarchar(255),
    out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_newitemid int;

  select @var_newitemid = max(ITEMID) from ADDRLSTITEMS where 1 = 1;
  if (@var_newitemid is null) set @var_newitemid = 0;
  set @var_newitemid = @var_newitemid + 1;

  insert into ADDRLSTITEMS(
    ITEMID,
    CATEGORYID,
    USERID,
    ACTUALNAME,
    SEX,
    AGE,
    ZIPCODE,
    ADDRESS,
    TELEPHONE,
    TELEPHONE_SHORT,
    TELEPHONE2,
    TELEPHONE2_SHORT,
    MOBILE,
    MOBILE_SHORT,
    MOBILE2,
    MOBILE2_SHORT,
    FAX,
    QQ,
    WEIXIN,
    WEIBO,
    EMAIL,
    ROOMNAME,
    ROOMNUMBER,
    IDNUMBER,
    CARNUMBER,
    DESCRIPTION,
    CREATETIME
    )
    values(
    @var_newitemid,    
    @in_categoryid,
    @in_userid,
    @in_actualname,
    @in_sex,
    @in_age,
    @in_zipcode,
    @in_address,
    @in_telephone,
    @in_telephone_short,
    @in_telephone2,
    @in_telephone2_short,
    @in_mobile,
    @in_mobile_short,
    @in_mobile2,
    @in_mobile2_short,
    @in_fax,
    @in_qq,
    @in_weixin,
    @in_weibo,
    @in_email,
    @in_roomname,
    @in_roomnumber,
    @in_idnumber,
    @in_carnumber,
    @in_description,
    getdate()
    );

  SET NOCOUNT OFF;

  select ITEMID as out_itemid, CATEGORYID as out_categoryid, USERID as out_userid, ACTUALNAME as out_actualname, 
       SEX as out_sex, AGE as out_age, ZIPCODE as out_zipcode, ADDRESS as out_address, TELEPHONE as out_telephone, 
       TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, TELEPHONE2_SHORT as out_telephone2_short, 
       MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, 
       FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, 
       ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, CARNUMBER as out_carnumber, DESCRIPTION as out_description, CREATETIME as out_createtime
       from ADDRLSTITEMS where ITEMID = @var_newitemid and USERID = @in_userid;
end;
GO

/* MODIFYADDRLSTITEM */

CREATE PROCEDURE MODIFYADDRLSTITEM 
    @in_userid int,
    @in_itemid int,
    @in_actualname nvarchar(20),
    @in_sex smallint,
    @in_age smallint,
    @in_zipcode nvarchar(10),
    @in_address nvarchar(50),
    @in_telephone nvarchar(20),
    @in_telephone_short nvarchar(20),
    @in_telephone2 nvarchar(20),
    @in_telephone2_short nvarchar(20),
    @in_mobile nvarchar(20),
    @in_mobile_short nvarchar(20),
    @in_mobile2 nvarchar(20),
    @in_mobile2_short nvarchar(20),
    @in_fax nvarchar(20),
    @in_qq nvarchar(50),
    @in_weixin nvarchar(50),
    @in_weibo nvarchar(50),
    @in_email nvarchar(50),
    @in_roomname nvarchar(50),
    @in_roomnumber nvarchar(50),
    @in_idnumber nvarchar(50),
    @in_carnumber nvarchar(50),
    @in_description nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update ADDRLSTITEMS set 
    ACTUALNAME = @in_actualname,
    SEX = @in_sex,
    AGE = @in_age,
    ZIPCODE = @in_zipcode,
    ADDRESS = @in_address,
    TELEPHONE = @in_telephone,
    TELEPHONE_SHORT = @in_telephone_short,
    TELEPHONE2 = @in_telephone2,
    TELEPHONE2_SHORT = @in_telephone2_short,
    MOBILE = @in_mobile,
    MOBILE_SHORT = @in_mobile_short,
    MOBILE2 = @in_mobile2,
    MOBILE2_SHORT = @in_mobile2_short,
    FAX = @in_fax,
    QQ = @in_qq,
    WEIXIN = @in_weixin,
    WEIBO = @in_weibo,
    EMAIL = @in_email,
    ROOMNAME = @in_roomname,
    ROOMNUMBER = @in_roomnumber,
    IDNUMBER = @in_idnumber,
    CARNUMBER = @in_carnumber,
    DESCRIPTION = @in_description
    where ITEMID = @in_itemid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEADDRLSTITEMCATEGORY */

CREATE PROCEDURE CHANGEADDRLSTITEMCATEGORY 
    @in_userid int,
    @in_itemid int,
    @in_categoryid int
as
begin
  SET NOCOUNT ON;

  update ADDRLSTITEMS set CATEGORYID = @in_categoryid where ITEMID = @in_itemid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* REMOVEADDRLSTITEM */

CREATE PROCEDURE REMOVEADDRLSTITEM 
    @in_userid int,
    @in_itemid int
as
begin
  SET NOCOUNT ON;

  delete from ADDRLSTITEMS where ITEMID = @in_itemid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETADDRLSTITEMS */

CREATE PROCEDURE GETADDRLSTITEMS 
    @in_userid int,
    @in_categoryid int
/*
returns (
    out_itemid int,
    out_categoryid int,
    out_userid int,
    out_actualname varchar(20),
    out_sex smallint,
    out_age smallint,
    out_zipcode varchar(10),
    out_address varchar(50),
    out_telephone varchar(20),
    out_telephone_short varchar(20),
    out_telephone2 varchar(20),
    out_telephone2_short varchar(20),
    out_mobile varchar(20),
    out_mobile_short varchar(20),
    out_mobile2 varchar(20),
    out_mobile2_short varchar(20),
    out_fax varchar(20),
    out_qq varchar(50),
    out_weixin varchar(50),
    out_weibo varchar(50),
    out_email varchar(50),
    out_roomname varchar(50),
    out_roomnumber varchar(50),
    out_idnumber varchar(50),
    out_carnumber varchar(50),   
    out_description varchar(255),
    out_createtime datetime)
*/
as
begin
  if (@in_categoryid >= 0)
  begin
      select ITEMID as out_itemid, CATEGORYID as out_categoryid, USERID as out_userid, ACTUALNAME as out_actualname, 
        SEX as out_sex, AGE as out_age, ZIPCODE as out_zipcode, ADDRESS as out_address, TELEPHONE as out_telephone, 
        TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, TELEPHONE2_SHORT as out_telephone2_short, 
	MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, 
	FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, 
	ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, CARNUMBER as out_carnumber, DESCRIPTION as out_description, CREATETIME as out_createtime
        from ADDRLSTITEMS where CATEGORYID = @in_categoryid and USERID = @in_userid order by CREATETIME ASC;
  end
  else
  begin
      select ITEMID as out_itemid, CATEGORYID as out_categoryid, USERID as out_userid, ACTUALNAME as out_actualname, 
        SEX as out_sex, AGE as out_age, ZIPCODE as out_zipcode, ADDRESS as out_address, TELEPHONE as out_telephone, 
        TELEPHONE_SHORT as out_telephone_short, TELEPHONE2 as out_telephone2, TELEPHONE2_SHORT as out_telephone2_short, 
	MOBILE as out_mobile, MOBILE_SHORT as out_mobile_short, MOBILE2 as out_mobile2, MOBILE2_SHORT as out_mobile2_short, 
	FAX as out_fax, QQ as out_qq, WEIXIN as out_weixin, WEIBO as out_weibo, EMAIL as out_email, ROOMNAME as out_roomname, 
	ROOMNUMBER as out_roomnumber, IDNUMBER as out_idnumber, CARNUMBER as out_carnumber, DESCRIPTION as out_description, CREATETIME as out_createtime
        from ADDRLSTITEMS where USERID = @in_userid order by CREATETIME ASC;
  end
end;
GO

/* ADDGROUP */

CREATE PROCEDURE ADDGROUP 
    @in_groupname nvarchar(20),
    @in_introduction nvarchar(255),
    @in_notice nvarchar(255),
    @in_joinsetting smallint,
    @in_creatorid int
/*
returns (
    @out_errorcode int,

    @out_groupid int,
    @out_groupname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime,
    
    @out_me_ismanager smallint,
    @out_me_lastrecvmsgsid bigint,
    @out_me_msghintsetting smallint,
    @out_me_modifycardbymng smallint,
    @out_me_card_name nvarchar(20),
    @out_me_card_sex smallint,
    @out_me_card_number nvarchar(20),
    @out_me_card_email nvarchar(50),
    @out_me_card_remark nvarchar(255),
    @out_me_jointime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_usertype smallint;
  declare @var_newgroupid int;
  declare @var_groupcount int;

  set @out_errorcode = 0;

  set @var_usertype = null;

  select @var_usertype = USERTYPE
    from USERS where USERID = @in_creatorid;

  if (@var_usertype is null) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  if (@var_usertype = 0)  /*USERTYPE_PUBLIC*/
  begin
    select @var_groupcount = count(*) from GROUPS 
       where OWNERID = @in_creatorid;

    if (@var_groupcount >= 3) 
    begin
      set @out_errorcode = 2;
      goto result_label;
    end
  end

  select @var_newgroupid = max(GROUPID) from GROUPS;
  if (@var_newgroupid is null) set @var_newgroupid = 10000 - 1;
  set @var_newgroupid = @var_newgroupid + 1;

  insert into GROUPS(
	GROUPID,
	GROUPNAME,
	INTRODUCTION,
	NOTICE,
	JOINSETTING,
	CREATORID,
	OWNERID,
	OWNERUSERTYPE,
	CREATETIME
    )
    values(
      @var_newgroupid,
      @in_groupname,
      @in_introduction,
      @in_notice,
      @in_joinsetting,
      @in_creatorid,
      @in_creatorid,
      @var_usertype,
      getdate()
    );

  insert into GROUPMEMBERS(
	MEMBERID,
	GROUPID,
	ISMANAGER,
	LASTRECVMSGSID,
	MSGHINTSETTING,
	MODIFYCARDBYMNG,
	CARD_NAME,
	CARD_SEX,
	CARD_NUMBER,
	CARD_EMAIL,
	CARD_REMARK,	
	JOINTIME
    )
    values(
      @in_creatorid,
      @var_newgroupid,
      1,
      0,
      1,
      1,
      '',
      0,
      '',
      '',
      '',
      getdate()
    );

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select top 1 @out_errorcode as out_errorcode, b.GROUPID as out_groupid, b.GROUPNAME as out_groupname, b.INTRODUCTION as out_introduction, 
       b.NOTICE as out_notice, b.JOINSETTING as out_joinsetting, b.CREATORID as out_creatorid, b.OWNERID as out_ownerid,
       b.OWNERUSERTYPE as out_ownerusertype, b.CREATETIME as out_createtime,
       a.ISMANAGER as out_me_ismanager, a.LASTRECVMSGSID as out_me_lastrecvmsgsid, a.MSGHINTSETTING as out_me_msghintsetting, 
       a.MODIFYCARDBYMNG as out_me_modifycardbymng, a.CARD_NAME as out_me_card_name, a.CARD_SEX as out_me_card_sex,
       a.CARD_NUMBER as out_me_card_number, a.CARD_EMAIL as out_me_card_email, a.CARD_REMARK as out_me_card_remark, a.JOINTIME as out_me_jointime
       from GROUPMEMBERS as a inner join GROUPS as b on a.GROUPID = b.GROUPID
       where a.MEMBERID = @in_creatorid and a.GROUPID = @var_newgroupid;
  return;
end;
GO

/* MODIFYGROUP */

CREATE PROCEDURE MODIFYGROUP 
    @in_groupid int,
    @in_groupname nvarchar(20),
    @in_introduction nvarchar(255),
    @in_notice nvarchar(255),
    @in_joinsetting smallint
as
begin
  SET NOCOUNT ON;

  update GROUPS set GROUPNAME = @in_groupname, INTRODUCTION = @in_introduction, 
      NOTICE = @in_notice, JOINSETTING = @in_joinsetting
      where GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* REMOVEGROUP */

CREATE PROCEDURE REMOVEGROUP 
    @in_groupid int
as
begin
  SET NOCOUNT ON;

  delete from GROUPSHAREFILES where GROUPID = @in_groupid;
  delete from GROUPMEMBERS where GROUPID = @in_groupid;
  delete from GROUPS where GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEGROUPOWNER */

CREATE PROCEDURE CHANGEGROUPOWNER 
    @in_groupid int,
    @in_newownerid int
/*
returns (
    @out_errorcode int,
    @out_oldowner_userid int,
    @out_oldowner_usertype smallint,
    @out_newowner_usertype smallint)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @out_oldowner_userid int;
  declare @out_oldowner_usertype smallint;
  declare @out_newowner_usertype smallint;

  declare @var_groupcount int;

  set @out_errorcode = 0;

  set @out_newowner_usertype = null;

  select @out_newowner_usertype = USERTYPE
    from USERS where USERID = @in_newownerid;

  if (@out_newowner_usertype is null) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  set @out_oldowner_userid = null;
  set @out_oldowner_usertype = null;

  select @out_oldowner_userid = OWNERID, @out_oldowner_usertype = OWNERUSERTYPE
    from GROUPS where GROUPID = @in_groupid;

  if ((@out_oldowner_userid is null) or (@out_oldowner_usertype is null)) 
  begin
    set @out_errorcode = 2;
    goto result_label;
  end

  if ((@out_oldowner_usertype = 1) and (@out_newowner_usertype = 0)) 
  begin
    set @out_errorcode = 3;
    goto result_label;
  end

  if (@out_newowner_usertype = 0) 
  begin
    select @var_groupcount = count(*) from GROUPS 
      where OWNERID = @in_newownerid;

    if (@var_groupcount >= 3) 
    begin
      set @out_errorcode = 4;
      goto result_label;
    end
  end

  update GROUPS set OWNERID = @in_newownerid, OWNERUSERTYPE = @out_newowner_usertype where GROUPID = @in_groupid;

  update GROUPMEMBERS set ISMANAGER = 0 where GROUPID = @in_groupid and MEMBERID = @out_oldowner_userid;
  update GROUPMEMBERS set ISMANAGER = 1 where GROUPID = @in_groupid and MEMBERID = @in_newownerid;

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, @out_oldowner_userid as out_oldowner_userid,
      @out_oldowner_usertype as out_oldowner_usertype, @out_newowner_usertype as out_newowner_usertype;
  return;
end;
GO

/* SETGROUPMANAGER */

CREATE PROCEDURE SETGROUPMANAGER 
    @in_groupid int,
    @in_memberid int,
    @in_ismanager smallint
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_usertype smallint;
  declare @var_owneruserid int;
  declare @var_ownerusertype smallint;
  declare @var_managercount int;

  set @out_errorcode = 0;

  if (not exists(select * from GROUPMEMBERS where MEMBERID = @in_memberid and GROUPID = @in_groupid)) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  set @var_usertype = null;

  select @var_usertype = USERTYPE from USERS where USERID = @in_memberid;

  if (@var_usertype is null) 
  begin
    set @out_errorcode = 2;
    goto result_label;
  end

  set @var_ownerusertype = null;

  select @var_owneruserid = OWNERID, @var_ownerusertype = OWNERUSERTYPE from GROUPS
    where GROUPID = @in_groupid;

  if ((@var_owneruserid is null) or (@var_ownerusertype is null)) 
  begin
    set @out_errorcode = 3;
    goto result_label;
  end

  if ((@var_ownerusertype = 1) and (@var_usertype = 0)) 
  begin
    set @out_errorcode = 4;
    goto result_label;
  end

  if (@in_ismanager = 1) 
  begin
      select @var_managercount = count(*) from GROUPMEMBERS where GROUPID = @in_groupid 
        and MEMBERID <> @in_memberid and ISMANAGER = 1;

      if (@var_managercount >= 4) 
      begin
        set @out_errorcode = 5;
        goto result_label;
      end

      update GROUPMEMBERS set ISMANAGER = 1 where MEMBERID = @in_memberid and GROUPID = @in_groupid;
  end
  else
  begin
      if (@var_owneruserid = @in_memberid) 
      begin
        set @out_errorcode = 6;
        goto result_label;      
      end
      update GROUPMEMBERS set ISMANAGER = 0 where MEMBERID = @in_memberid and GROUPID = @in_groupid;
  end

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* GETUSERGROUPS */

CREATE PROCEDURE GETUSERGROUPS 
    @in_userid int
/*
returns (
    @out_groupid int,
    @out_groupname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime,

    @out_me_ismanager smallint,
    @out_me_lastrecvmsgsid bigint,
    @out_me_msghintsetting smallint,
    @out_me_modifycardbymng smallint,
    @out_me_card_name nvarchar(20),
    @out_me_card_sex smallint,
    @out_me_card_number nvarchar(20),
    @out_me_card_email nvarchar(50),
    @out_me_card_remark nvarchar(255),
    @out_me_jointime datetime)
*/
as
begin
  select b.GROUPID as out_groupid, b.GROUPNAME as out_groupname, b.INTRODUCTION as out_introduction, 
       b.NOTICE as out_notice, b.JOINSETTING as out_joinsetting, b.CREATORID as out_creatorid, b.OWNERID as out_ownerid,
       b.OWNERUSERTYPE as out_ownerusertype, b.CREATETIME as out_createtime,
       a.ISMANAGER as out_me_ismanager, a.LASTRECVMSGSID as out_me_lastrecvmsgsid, a.MSGHINTSETTING as out_me_msghintsetting, 
       a.MODIFYCARDBYMNG as out_me_modifycardbymng, a.CARD_NAME as out_me_card_name, a.CARD_SEX as out_me_card_sex, 
       a.CARD_NUMBER as out_me_card_number, a.CARD_EMAIL as out_me_card_email, a.CARD_REMARK as out_me_card_remark, 
       a.JOINTIME as out_me_jointime from GROUPMEMBERS as a inner join GROUPS as b on a.GROUPID = b.GROUPID
       where a.MEMBERID = @in_userid;
end;
GO

/* GETALLGROUPS */

CREATE PROCEDURE GETALLGROUPS
/*
returns (
    @out_groupid int,
    @out_groupname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime)
*/
as
begin
  select GROUPID as out_groupid, GROUPNAME as out_groupname, INTRODUCTION as out_introduction, NOTICE as out_notice, 
       JOINSETTING as out_joinsetting, CREATORID as out_creatorid, OWNERID as out_ownerid, 
       OWNERUSERTYPE as out_ownerusertype, CREATETIME as out_createtime
       from GROUPS where 1 = 1;
end;
GO

/* ADDGROUPMEMBER */

CREATE PROCEDURE ADDGROUPMEMBER 
    @in_groupid int,
    @in_userid int
/*
returns (
    @out_memberid int,
    @out_groupid int,
    @out_ismanager smallint,
    @out_lastrecvmsgsid bigint,
    @out_msghintsetting smallint,
    @out_modifycardbymng smallint,
    @out_card_name nvarchar(20),
    @out_card_sex smallint,
    @out_card_number nvarchar(20),
    @out_card_email nvarchar(50),
    @out_card_remark nvarchar(255),
    @out_jointime datetime,
    
    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint)
*/
as
begin
  SET NOCOUNT ON;

  insert into GROUPMEMBERS(
	MEMBERID,
	GROUPID,
	ISMANAGER,
	LASTRECVMSGSID,
	MSGHINTSETTING,
	MODIFYCARDBYMNG,
	CARD_NAME,
	CARD_SEX,
	CARD_NUMBER,
	CARD_EMAIL,
	CARD_REMARK,	
	JOINTIME
    )
    values(
      @in_userid,
      @in_groupid,
      0,
      0,
      1,
      1,
      '',
      0,
      '',
      '',
      '',
      getdate()
    );

  SET NOCOUNT OFF;

  select a.MEMBERID as out_memberid, a.GROUPID as out_groupid, a.ISMANAGER as out_ismanager, 
        a.LASTRECVMSGSID as out_lastrecvmsgsid, a.MSGHINTSETTING as out_msghintsetting, a.MODIFYCARDBYMNG as out_modifycardbymng,  
	a.CARD_NAME as out_card_name, a.CARD_SEX as out_card_sex, a.CARD_NUMBER as out_card_number, a.CARD_EMAIL as out_card_email, 
	a.CARD_REMARK as out_card_remark, a.JOINTIME as out_jointime, b.USERNAME as out_username, b.USERTYPE as out_usertype,
	b.NICKNAME as out_nickname, b.SIGNATURE as out_signature, 
	b.ACTUALNAME as out_actualname, b.SEX as out_sex, b.AGE as out_age, b.BIRTH_YEAR as out_birth_year, 
	b.BIRTH_MONTH as out_birth_month, b.BIRTH_DAY as out_birth_day, b.COUNTRY as out_country, b.PROVINCE as out_province, 
	b.CITY as out_city, b.AREA as out_area,  b.ADDRESS as out_address, b.TELEPHONE as out_telephone, 
	b.TELEPHONE_SHORT as out_telephone_short, b.TELEPHONE2 as out_telephone2, 
       b.TELEPHONE2_SHORT as out_telephone2_short, b.MOBILE as out_mobile, b.MOBILE_SHORT as out_mobile_short, 
       b.MOBILE2 as out_mobile2, b.MOBILE2_SHORT as out_mobile2_short, b.FAX as out_fax, b.QQ as out_qq, b.WEIXIN as out_weixin, 
       b.WEIBO as out_weibo, b.EMAIL as out_email, b.ROOMNAME as out_roomname, b.ROOMNUMBER as out_roomnumber, b.IDNUMBER as out_idnumber, 
       b.CARNUMBER as out_carnumber, b.HOMEPAGE as out_homepage, b.DEPARTID as out_departid, b.DEPARTNAME as out_departname, 
       b.JOBTITLE as out_jobtitle, b.JOBNUMBER as out_jobnumber, b.INTRODUCTION as out_introduction, 
       b.FACEFILE as out_facefile, b.PHOTOFILE as out_photofile, b.LOGINSTATUS as out_loginstatus
	from GROUPMEMBERS as a inner join USERS as b on a.MEMBERID = b.USERID 
	where a.MEMBERID = @in_userid and a.GROUPID = @in_groupid;
end;
GO

/* REMOVEGROUPMEMBER */

CREATE PROCEDURE REMOVEGROUPMEMBER 
    @in_groupid int,
    @in_userid int
as
begin
  SET NOCOUNT ON;

  delete from GROUPSHAREFILES where MEMBERID = @in_userid and GROUPID = @in_groupid;
  delete from GROUPMEMBERS where MEMBERID = @in_userid and GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* MODIFYGROUPMEMBER */

CREATE PROCEDURE MODIFYGROUPMEMBER 
    @in_groupid int,
    @in_memberid int,
    @in_msghintsetting smallint,
    @in_modifycardbymng smallint,
    @in_card_name nvarchar(20),
    @in_card_sex smallint,
    @in_card_number nvarchar(20),
    @in_card_email nvarchar(50),
    @in_card_remark nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update GROUPMEMBERS set MSGHINTSETTING = @in_msghintsetting, MODIFYCARDBYMNG = @in_modifycardbymng, 
	CARD_NAME = @in_card_name, CARD_SEX = @in_card_sex, CARD_NUMBER = @in_card_number, 
	CARD_EMAIL = @in_card_email, CARD_REMARK = @in_card_remark
	where MEMBERID = @in_memberid and GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* UPDATEGROUPLASTRECVMSGSID */

CREATE PROCEDURE UPDATEGROUPLASTRECVMSGSID 
    @in_groupid int,
    @in_memberid int
as
begin
  SET NOCOUNT ON;

  declare @var_lastrecvmsgsid bigint;

  select @var_lastrecvmsgsid = MAX(SID) from OFFGROUPMSGS 
	where GROUPID = @in_groupid;
  if (@var_lastrecvmsgsid is null)  return;
  update GROUPMEMBERS set LASTRECVMSGSID = @var_lastrecvmsgsid 
	where MEMBERID = @in_memberid and GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* GETGROUPMEMBERS */

CREATE PROCEDURE GETGROUPMEMBERS 
    @in_groupid int
/*
returns (
    @out_memberid int,
    @out_groupid int,
    @out_ismanager smallint,
    @out_lastrecvmsgsid bigint,
    @out_msghintsetting smallint,
    @out_modifycardbymng smallint,
    @out_card_name nvarchar(20),
    @out_card_sex smallint,
    @out_card_number nvarchar(20),
    @out_card_email nvarchar(50),
    @out_card_remark nvarchar(255),
    @out_jointime datetime,

    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint)
*/
as
begin
  select a.MEMBERID as out_memberid, a.GROUPID as out_groupid, a.ISMANAGER as out_ismanager, a.LASTRECVMSGSID as out_lastrecvmsgsid,
        a.MSGHINTSETTING as out_msghintsetting, a.MODIFYCARDBYMNG as out_modifycardbymng, a.CARD_NAME as out_card_name, 
	a.CARD_SEX as out_card_sex, a.CARD_NUMBER as out_card_number, a.CARD_EMAIL as out_card_email, a.CARD_REMARK as out_card_remark, 
	a.JOINTIME as out_jointime, b.USERNAME as out_username, b.USERTYPE as out_usertype, b.NICKNAME as out_nickname, 
	b.SIGNATURE as out_signature, b.ACTUALNAME as out_actualname, b.SEX as out_sex, b.AGE as out_age, b.BIRTH_YEAR as out_birth_year, 
	b.BIRTH_MONTH as out_birth_month, b.BIRTH_DAY as out_birth_day, b.COUNTRY as out_country, b.PROVINCE as out_province, 
	b.CITY as out_city, b.AREA as out_area, b.ADDRESS as out_address, b.TELEPHONE as out_telephone, 
	b.TELEPHONE_SHORT as out_telephone_short, b.TELEPHONE2 as out_telephone2, 
       b.TELEPHONE2_SHORT as out_telephone2_short, b.MOBILE as out_mobile, b.MOBILE_SHORT as out_mobile_short, 
       b.MOBILE2 as out_mobile2, b.MOBILE2_SHORT as out_mobile2_short, b.FAX as out_fax, b.QQ as out_qq, b.WEIXIN as out_weixin, 
       b.WEIBO as out_weibo, b.EMAIL as out_email, b.ROOMNAME as out_roomname, b.ROOMNUMBER as out_roomnumber, b.IDNUMBER as out_idnumber, 
       b.CARNUMBER as out_carnumber, b.HOMEPAGE as out_homepage, b.DEPARTID as out_departid, b.DEPARTNAME as out_departname, 
       b.JOBTITLE as out_jobtitle, b.JOBNUMBER as out_jobnumber, b.INTRODUCTION as out_introduction, 
       b.FACEFILE as out_facefile, b.PHOTOFILE as out_photofile, b.LOGINSTATUS as out_loginstatus
	from GROUPMEMBERS as a inner join USERS as b on a.MEMBERID = b.USERID where a.GROUPID = @in_groupid;
end;
GO

/* ADDGROUPSHAREFILE */

CREATE PROCEDURE ADDGROUPSHAREFILE 
    @in_sid varchar(50),
    @in_memberid int,
    @in_groupid int,
    @in_filenameordir nvarchar(255),
    @in_isdir smallint,
    @in_allfilecount int,
    @in_allfilesize bigint
as
begin
  SET NOCOUNT ON;

  insert into GROUPSHAREFILES (
	SID,
	MEMBERID,
	GROUPID,
	FILENAMEORDIR,
	ISDIR,
        ALLFILECOUNT,
	ALLFILESIZE,
	CREATETIME
    )
    values(
        @in_sid,
	@in_memberid,
	@in_groupid,
	@in_filenameordir,
	@in_isdir,
	@in_allfilecount,
	@in_allfilesize,
	getdate()
    );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEGROUPSHAREFILE */

CREATE PROCEDURE REMOVEGROUPSHAREFILE 
    @in_groupid int,
    @in_sid varchar(50)
as
begin
  SET NOCOUNT ON;

  delete from GROUPSHAREFILES where SID = @in_sid and GROUPID = @in_groupid;

  SET NOCOUNT OFF;
end;
GO

/* GETGROUPSHAREFILES */

CREATE PROCEDURE GETGROUPSHAREFILES 
    @in_groupid int
/*
returns (
    @out_sid varchar(50),
    @out_memberid int,
    @out_member_nickname nvarchar(20),
    @out_member_actualname nvarchar(20),
    @out_groupid int,
    @out_filenameordir nvarchar(255),
    @out_isdir smallint,
    @out_allfilecount int,
    @out_allfilesize bigint,
    @out_createtime datetime)
*/
as
begin
  select a.SID as out_sid, a.MEMBERID as out_memberid, b.NICKNAME as out_member_nickname, b.ACTUALNAME as out_member_actualname, 
        a.GROUPID as out_groupid, a.FILENAMEORDIR as out_filenameordir, a.ISDIR as out_isdir, a.ALLFILECOUNT as out_allfilecount, 
	a.ALLFILESIZE as out_allfilesize, a.CREATETIME as out_createtime 
	from GROUPSHAREFILES as a inner join USERS as b on a.MEMBERID = b.USERID 
	where a.GROUPID = @in_groupid;
end;
GO

/* ADDJOINGROUPREQUEST */

CREATE PROCEDURE ADDJOINGROUPREQUEST 
    @in_userid int,
    @in_groupid int,
    @in_content nvarchar(255),
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINGROUPREQUESTS 
	where USERID = @in_userid and GROUPID = @in_groupid and SENDER = @in_sender)) 
  begin
    update JOINGROUPREQUESTS set CONTENT = @in_content 
      where USERID = @in_userid and GROUPID = @in_groupid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINGROUPREQUESTS(USERID, GROUPID, CONTENT, SENDER, SENDTIME) 
      values(@in_userid, @in_groupid, @in_content, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINGROUPREQUESTS */

CREATE PROCEDURE GETJOINGROUPREQUESTS 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_groupid int,
    @out_content nvarchar(255),
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, GROUPID as out_groupid, CONTENT as out_content, SENDER as out_sender, SENDTIME as out_sendtime
	from JOINGROUPREQUESTS where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINGROUPREQUESTS where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDJOINGROUPRESPONSE */

CREATE PROCEDURE ADDJOINGROUPRESPONSE 
    @in_userid int,
    @in_groupid int,
    @in_content nvarchar(255),
    @in_agree smallint,
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINGROUPRESPONSES 
	where USERID = @in_userid and GROUPID = @in_groupid and SENDER = @in_sender)) 
  begin
    update JOINGROUPRESPONSES set CONTENT = @in_content, AGREE = @in_agree 
      where USERID = @in_userid and GROUPID = @in_groupid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINGROUPRESPONSES(USERID, GROUPID, CONTENT, AGREE, SENDER, SENDTIME) 
      values(@in_userid, @in_groupid, @in_content, @in_agree, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINGROUPRESPONSES */

CREATE PROCEDURE GETJOINGROUPRESPONSES 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_groupid int,
    @out_content nvarchar(255),
    @out_agree smallint,
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, GROUPID as out_groupid, CONTENT, 
        AGREE as out_agree, SENDER as out_sender, SENDTIME as out_sendtime
	from JOINGROUPRESPONSES where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINGROUPRESPONSES where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDOFFGROUPMSG */

CREATE PROCEDURE ADDOFFGROUPMSG 
    @in_groupid int,
    @in_msgid varchar(50),
    @in_msglevel smallint,
    @in_content nvarchar(4000),
    @in_font nvarchar(50),
    @in_sender_userid int,
    @in_sender_nickname nvarchar(20),
    @in_sender_actualname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  delete from OFFGROUPMSGS where GROUPID = @in_groupid and SENDTIME > dateadd(day, 7, getdate());

  insert into OFFGROUPMSGS(
    GROUPID,
    MSGID,
    MSGLEVEL,
    CONTENT,
    FONT,
    SENDER_USERID,
    SENDER_NICKNAME,
    SENDER_ACTUALNAME,
    SENDTIME
  ) values (
    @in_groupid,
    @in_msgid,
    @in_msglevel,
    @in_content,
    @in_font,
    @in_sender_userid,
    @in_sender_nickname,
    @in_sender_actualname,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFGROUPMSG */

CREATE PROCEDURE REMOVEOFFGROUPMSG 
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from OFFGROUPMSGS where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFGROUPMSGS */

CREATE PROCEDURE GETOFFGROUPMSGS 
    @in_groupid int,
    @in_lastread_sid bigint
/*
returns (
    @out_sid bigint,
    @out_groupid int,
    @out_msgid varchar(50),
    @out_msglevel smallint,
    @out_content nvarchar(8192),
    @out_font nvarchar(50),
    @out_sender_userid int,
    @out_sender_nickname nvarchar(20),
    @out_sender_actualname nvarchar(20),
    @out_sendtime datetime)
*/
as
begin
  select top 200 SID as out_sid, GROUPID as out_groupid, MSGID as out_msgid, MSGLEVEL as out_msglevel, 
      CONTENT as out_content, FONT as out_font, SENDER_USERID as out_sender_userid, SENDER_NICKNAME as out_sender_nickname, 
      SENDER_ACTUALNAME as out_sender_actualname, SENDTIME as out_sendtime 
      from OFFGROUPMSGS where GROUPID = @in_groupid and SID > @in_lastread_sid order by SID ASC;
end;
GO

/* ADDOFFGROUPSUPEROBJ */

CREATE PROCEDURE ADDOFFGROUPSUPEROBJ 
    @in_groupid int,
    @in_msgid varchar(50),
    @in_fromuserid int,
    @in_superobjectcode nvarchar(1024),
    @in_localfilename nvarchar(1024)
as
begin
  SET NOCOUNT ON;

  delete from OFFGROUPSUPEROBJS where GROUPID = @in_groupid and CREATETIME > dateadd(day, 7, getdate());

  insert into OFFGROUPSUPEROBJS(
    GROUPID,
    MSGID,
    FROMUSERID,
    SUPEROBJCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    @in_groupid,
    @in_msgid,
    @in_fromuserid,
    @in_superobjectcode,
    @in_localfilename,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFGROUPSUPEROBJ */

CREATE PROCEDURE REMOVEOFFGROUPSUPEROBJ 
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from OFFGROUPSUPEROBJS where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFGROUPSUPEROBJS */

CREATE PROCEDURE GETOFFGROUPSUPEROBJS 
    @in_groupid int,
    @in_msgid varchar(50),
    @in_fromuserid int
/*
returns (
    @out_sid bigint,
    @out_groupid int,
    @out_msgid nvarchar(50),
    @out_fromuserid int,
    @out_superobjectcode nvarchar(1024),
    @out_localfilename nvarchar(1024),
    @out_createtime datetime)
*/
as
begin
  select SID as out_sid, GROUPID as out_groupid, MSGID as out_msgid, FROMUSERID as out_fromuserid, 
    SUPEROBJCODE as out_superobjectcode, LOCALFILENAME as out_localfilename, CREATETIME as out_createtime
    from OFFGROUPSUPEROBJS where GROUPID = @in_groupid and MSGID = @in_msgid and FROMUSERID = @in_fromuserid;
end;
GO

/* ADDROOM */

CREATE PROCEDURE ADDROOM 
    @in_roomname nvarchar(20),
    @in_introduction nvarchar(255),
    @in_notice nvarchar(255),
    @in_joinsetting smallint,
    @in_creatorid int
/*
returns (
    @out_errorcode int,

    @out_roomid int,
    @out_roomname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime,
    
    @out_me_ismanager smallint,
    @out_me_lastrecvmsgsid bigint,
    @out_me_msghintsetting smallint,
    @out_me_modifycardbymng smallint,
    @out_me_card_name nvarchar(20),
    @out_me_card_sex smallint,
    @out_me_card_number nvarchar(20),
    @out_me_card_email nvarchar(50),
    @out_me_card_remark nvarchar(255),
    @out_me_jointime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_usertype smallint;
  declare @var_newroomid int;
  declare @var_roomcount int;

  set @out_errorcode = 0;

  set @var_usertype = null;

  select @var_usertype = USERTYPE
    from USERS where USERID = @in_creatorid;

  if (@var_usertype is null) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  if (@var_usertype = 0)  /*USERTYPE_PUBLIC*/
  begin
    select @var_roomcount = count(*) from ROOMS 
       where OWNERID = @in_creatorid;

    if (@var_roomcount >= 3) 
    begin
      set @out_errorcode = 2;
      goto result_label;
    end
  end

  select @var_newroomid = max(ROOMID) from ROOMS;
  if (@var_newroomid is null) set @var_newroomid = 10000 - 1;
  set @var_newroomid = @var_newroomid + 1;

  insert into ROOMS(
	ROOMID,
	ROOMNAME,
	INTRODUCTION,
	NOTICE,
	JOINSETTING,
	CREATORID,
	OWNERID,
	OWNERUSERTYPE,
	CREATETIME
    )
    values(
      @var_newroomid,
      @in_roomname,
      @in_introduction,
      @in_notice,
      @in_joinsetting,
      @in_creatorid,
      @in_creatorid,
      @var_usertype,
      getdate()
    );

  insert into ROOMMEMBERS(
	MEMBERID,
	ROOMID,
	ISMANAGER,
	LASTRECVMSGSID,
	MSGHINTSETTING,
	MODIFYCARDBYMNG,
	CARD_NAME,
	CARD_SEX,
	CARD_NUMBER,
	CARD_EMAIL,
	CARD_REMARK,	
	JOINTIME
    )
    values(
      @in_creatorid,
      @var_newroomid,
      1,
      0,
      1,
      1,
      '',
      0,
      '',
      '',
      '',
      getdate()
    );

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select top 1 @out_errorcode as out_errorcode, b.ROOMID as out_roomid, b.ROOMNAME as out_roomname, b.INTRODUCTION as out_introduction, 
       b.NOTICE as out_notice, b.JOINSETTING as out_joinsetting, b.CREATORID as out_creatorid, b.OWNERID as out_ownerid,
       b.OWNERUSERTYPE as out_ownerusertype, b.CREATETIME as out_createtime,
       a.ISMANAGER as out_me_ismanager, a.LASTRECVMSGSID as out_me_lastrecvmsgsid, a.MSGHINTSETTING as out_me_msghintsetting, 
       a.MODIFYCARDBYMNG as out_me_modifycardbymng, a.CARD_NAME as out_me_card_name, a.CARD_SEX as out_me_card_sex,
       a.CARD_NUMBER as out_me_card_number, a.CARD_EMAIL as out_me_card_email, a.CARD_REMARK as out_me_card_remark, a.JOINTIME as out_me_jointime
       from ROOMMEMBERS as a inner join ROOMS as b on a.ROOMID = b.ROOMID
       where a.MEMBERID = @in_creatorid and a.ROOMID = @var_newroomid;
  return;
end;
GO

/* MODIFYROOM */

CREATE PROCEDURE MODIFYROOM 
    @in_roomid int,
    @in_roomname nvarchar(20),
    @in_introduction nvarchar(255),
    @in_notice nvarchar(255),
    @in_joinsetting smallint
as
begin
  SET NOCOUNT ON;

  update ROOMS set ROOMNAME = @in_roomname, INTRODUCTION = @in_introduction, 
      NOTICE = @in_notice, JOINSETTING = @in_joinsetting
      where ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* REMOVEROOM */

CREATE PROCEDURE REMOVEROOM 
    @in_roomid int
as
begin
  SET NOCOUNT ON;

  delete from ROOMSHAREFILES where ROOMID = @in_roomid;
  delete from ROOMMEMBERS where ROOMID = @in_roomid;
  delete from ROOMS where ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEROOMOWNER */

CREATE PROCEDURE CHANGEROOMOWNER 
    @in_roomid int,
    @in_newownerid int
/*
returns (
    @out_errorcode int,
    @out_oldowner_userid int,
    @out_oldowner_usertype smallint,
    @out_newowner_usertype smallint)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @out_oldowner_userid int;
  declare @out_oldowner_usertype smallint;
  declare @out_newowner_usertype smallint;

  declare @var_roomcount int;

  set @out_errorcode = 0;

  set @out_newowner_usertype = null;

  select @out_newowner_usertype = USERTYPE
    from USERS where USERID = @in_newownerid;

  if (@out_newowner_usertype is null) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  set @out_oldowner_userid = null;
  set @out_oldowner_usertype = null;

  select @out_oldowner_userid = OWNERID, @out_oldowner_usertype = OWNERUSERTYPE
    from ROOMS where ROOMID = @in_roomid;

  if ((@out_oldowner_userid is null) or (@out_oldowner_usertype is null)) 
  begin
    set @out_errorcode = 2;
    goto result_label;
  end

  if ((@out_oldowner_usertype = 1) and (@out_newowner_usertype = 0)) 
  begin
    set @out_errorcode = 3;
    goto result_label;
  end

  if (@out_newowner_usertype = 0) 
  begin
    select @var_roomcount = count(*) from ROOMS 
      where OWNERID = @in_newownerid;

    if (@var_roomcount >= 3) 
    begin
      set @out_errorcode = 4;
      goto result_label;
    end
  end

  update ROOMS set OWNERID = @in_newownerid, OWNERUSERTYPE = @out_newowner_usertype where ROOMID = @in_roomid;

  update ROOMMEMBERS set ISMANAGER = 0 where ROOMID = @in_roomid and MEMBERID = @out_oldowner_userid;
  update ROOMMEMBERS set ISMANAGER = 1 where ROOMID = @in_roomid and MEMBERID = @in_newownerid;

  goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, @out_oldowner_userid as out_oldowner_userid,
      @out_oldowner_usertype as out_oldowner_usertype, @out_newowner_usertype as out_newowner_usertype;
  return;
end;
GO

/* SETROOMMANAGER */

CREATE PROCEDURE SETROOMMANAGER 
    @in_roomid int,
    @in_memberid int,
    @in_ismanager smallint
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_usertype smallint;
  declare @var_owneruserid int;
  declare @var_ownerusertype smallint;
  declare @var_managercount int;

  set @out_errorcode = 0;

  if (not exists(select * from ROOMMEMBERS where MEMBERID = @in_memberid and ROOMID = @in_roomid)) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  set @var_usertype = null;

  select @var_usertype = USERTYPE from USERS where USERID = @in_memberid;

  if (@var_usertype is null) 
  begin
    set @out_errorcode = 2;
    goto result_label;
  end

  set @var_ownerusertype = null;

  select @var_owneruserid = OWNERID, @var_ownerusertype = OWNERUSERTYPE from ROOMS
    where ROOMID = @in_roomid;

  if ((@var_owneruserid is null) or (@var_ownerusertype is null)) 
  begin
    set @out_errorcode = 3;
    goto result_label;
  end

  if ((@var_ownerusertype = 1) and (@var_usertype = 0)) 
  begin
    set @out_errorcode = 4;
    goto result_label;
  end

  if (@in_ismanager = 1) 
  begin
      select @var_managercount = count(*) from ROOMMEMBERS where ROOMID = @in_roomid 
        and MEMBERID <> @in_memberid and ISMANAGER = 1;

      if (@var_managercount >= 4) 
      begin
        set @out_errorcode = 5;
        goto result_label;
      end

      update ROOMMEMBERS set ISMANAGER = 1 where MEMBERID = @in_memberid and ROOMID = @in_roomid;
  end
  else
  begin
      if (@var_owneruserid = @in_memberid) 
      begin
        set @out_errorcode = 6;
        goto result_label;      
      end
      update ROOMMEMBERS set ISMANAGER = 0 where MEMBERID = @in_memberid and ROOMID = @in_roomid;
  end

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* GETUSERROOMS */

CREATE PROCEDURE GETUSERROOMS 
    @in_userid int
/*
returns (
    @out_roomid int,
    @out_roomname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime,

    @out_me_ismanager smallint,
    @out_me_lastrecvmsgsid bigint,
    @out_me_msghintsetting smallint,
    @out_me_modifycardbymng smallint,
    @out_me_card_name nvarchar(20),
    @out_me_card_sex smallint,
    @out_me_card_number nvarchar(20),
    @out_me_card_email nvarchar(50),
    @out_me_card_remark nvarchar(255),
    @out_me_jointime datetime)
*/
as
begin
  select b.ROOMID as out_roomid, b.ROOMNAME as out_roomname, b.INTRODUCTION as out_introduction, 
       b.NOTICE as out_notice, b.JOINSETTING as out_joinsetting, b.CREATORID as out_creatorid, b.OWNERID as out_ownerid,
       b.OWNERUSERTYPE as out_ownerusertype, b.CREATETIME as out_createtime,
       a.ISMANAGER as out_me_ismanager, a.LASTRECVMSGSID as out_me_lastrecvmsgsid, a.MSGHINTSETTING as out_me_msghintsetting, 
       a.MODIFYCARDBYMNG as out_me_modifycardbymng, a.CARD_NAME as out_me_card_name, a.CARD_SEX as out_me_card_sex, 
       a.CARD_NUMBER as out_me_card_number, a.CARD_EMAIL as out_me_card_email, a.CARD_REMARK as out_me_card_remark, 
       a.JOINTIME as out_me_jointime from ROOMMEMBERS as a inner join ROOMS as b on a.ROOMID = b.ROOMID
       where a.MEMBERID = @in_userid;
end;
GO

/* GETALLROOMS */

CREATE PROCEDURE GETALLROOMS
/*
returns (
    @out_roomid int,
    @out_roomname nvarchar(20),
    @out_introduction nvarchar(255),
    @out_notice nvarchar(255),
    @out_joinsetting smallint,
    @out_creatorid int,
    @out_ownerid int,
    @out_ownerusertype smallint,
    @out_createtime datetime)
*/
as
begin
  select ROOMID as out_roomid, ROOMNAME as out_roomname, INTRODUCTION as out_introduction, NOTICE as out_notice, 
       JOINSETTING as out_joinsetting, CREATORID as out_creatorid, OWNERID as out_ownerid, 
       OWNERUSERTYPE as out_ownerusertype, CREATETIME as out_createtime
       from ROOMS where 1 = 1;
end;
GO

/* ADDROOMMEMBER */

CREATE PROCEDURE ADDROOMMEMBER 
    @in_roomid int,
    @in_userid int
/*
returns (
    @out_memberid int,
    @out_roomid int,
    @out_ismanager smallint,
    @out_lastrecvmsgsid bigint,
    @out_msghintsetting smallint,
    @out_modifycardbymng smallint,
    @out_card_name nvarchar(20),
    @out_card_sex smallint,
    @out_card_number nvarchar(20),
    @out_card_email nvarchar(50),
    @out_card_remark nvarchar(255),
    @out_jointime datetime,
    
    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint)
*/
as
begin
  SET NOCOUNT ON;

  insert into ROOMMEMBERS(
	MEMBERID,
	ROOMID,
	ISMANAGER,
	LASTRECVMSGSID,
	MSGHINTSETTING,
	MODIFYCARDBYMNG,
	CARD_NAME,
	CARD_SEX,
	CARD_NUMBER,
	CARD_EMAIL,
	CARD_REMARK,	
	JOINTIME
    )
    values(
      @in_userid,
      @in_roomid,
      0,
      0,
      1,
      1,
      '',
      0,
      '',
      '',
      '',
      getdate()
    );

  SET NOCOUNT OFF;

  select a.MEMBERID as out_memberid, a.ROOMID as out_roomid, a.ISMANAGER as out_ismanager, 
        a.LASTRECVMSGSID as out_lastrecvmsgsid, a.MSGHINTSETTING as out_msghintsetting, a.MODIFYCARDBYMNG as out_modifycardbymng,  
	a.CARD_NAME as out_card_name, a.CARD_SEX as out_card_sex, a.CARD_NUMBER as out_card_number, a.CARD_EMAIL as out_card_email, 
	a.CARD_REMARK as out_card_remark, a.JOINTIME as out_jointime, b.USERNAME as out_username, b.USERTYPE as out_usertype,
	b.NICKNAME as out_nickname, b.SIGNATURE as out_signature, 
	b.ACTUALNAME as out_actualname, b.SEX as out_sex, b.AGE as out_age, b.BIRTH_YEAR as out_birth_year, 
	b.BIRTH_MONTH as out_birth_month, b.BIRTH_DAY as out_birth_day, b.COUNTRY as out_country, b.PROVINCE as out_province, 
	b.CITY as out_city, b.AREA as out_area,  b.ADDRESS as out_address, b.TELEPHONE as out_telephone, 
	b.TELEPHONE_SHORT as out_telephone_short, b.TELEPHONE2 as out_telephone2, 
       b.TELEPHONE2_SHORT as out_telephone2_short, b.MOBILE as out_mobile, b.MOBILE_SHORT as out_mobile_short, 
       b.MOBILE2 as out_mobile2, b.MOBILE2_SHORT as out_mobile2_short, b.FAX as out_fax, b.QQ as out_qq, b.WEIXIN as out_weixin, 
       b.WEIBO as out_weibo, b.EMAIL as out_email, b.ROOMNAME as out_roomname, b.ROOMNUMBER as out_roomnumber, b.IDNUMBER as out_idnumber, 
       b.CARNUMBER as out_carnumber, b.HOMEPAGE as out_homepage, b.DEPARTID as out_departid, b.DEPARTNAME as out_departname, 
       b.JOBTITLE as out_jobtitle, b.JOBNUMBER as out_jobnumber, b.INTRODUCTION as out_introduction, 
       b.FACEFILE as out_facefile, b.PHOTOFILE as out_photofile, b.LOGINSTATUS as out_loginstatus
	from ROOMMEMBERS as a inner join USERS as b on a.MEMBERID = b.USERID 
	where a.MEMBERID = @in_userid and a.ROOMID = @in_roomid;
end;
GO

/* REMOVEROOMMEMBER */

CREATE PROCEDURE REMOVEROOMMEMBER 
    @in_roomid int,
    @in_userid int
as
begin
  SET NOCOUNT ON;

  delete from ROOMSHAREFILES where MEMBERID = @in_userid and ROOMID = @in_roomid;
  delete from ROOMMEMBERS where MEMBERID = @in_userid and ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* MODIFYROOMMEMBER */

CREATE PROCEDURE MODIFYROOMMEMBER 
    @in_roomid int,
    @in_memberid int,
    @in_msghintsetting smallint,
    @in_modifycardbymng smallint,
    @in_card_name nvarchar(20),
    @in_card_sex smallint,
    @in_card_number nvarchar(20),
    @in_card_email nvarchar(50),
    @in_card_remark nvarchar(255)
as
begin
  SET NOCOUNT ON;

  update ROOMMEMBERS set MSGHINTSETTING = @in_msghintsetting, MODIFYCARDBYMNG = @in_modifycardbymng, 
	CARD_NAME = @in_card_name, CARD_SEX = @in_card_sex, CARD_NUMBER = @in_card_number, 
	CARD_EMAIL = @in_card_email, CARD_REMARK = @in_card_remark
	where MEMBERID = @in_memberid and ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* UPDATEROOMLASTRECVMSGSID */

CREATE PROCEDURE UPDATEROOMLASTRECVMSGSID 
    @in_roomid int,
    @in_memberid int
as
begin
  SET NOCOUNT ON;

  declare @var_lastrecvmsgsid bigint;

  select @var_lastrecvmsgsid = MAX(SID) from OFFROOMMSGS 
	where ROOMID = @in_roomid;
  if (@var_lastrecvmsgsid is null)  return;
  update ROOMMEMBERS set LASTRECVMSGSID = @var_lastrecvmsgsid 
	where MEMBERID = @in_memberid and ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* GETROOMMEMBERS */

CREATE PROCEDURE GETROOMMEMBERS 
    @in_roomid int
/*
returns (
    @out_memberid int,
    @out_roomid int,
    @out_ismanager smallint,
    @out_lastrecvmsgsid bigint,
    @out_msghintsetting smallint,
    @out_modifycardbymng smallint,
    @out_card_name nvarchar(20),
    @out_card_sex smallint,
    @out_card_number nvarchar(20),
    @out_card_email nvarchar(50),
    @out_card_remark nvarchar(255),
    @out_jointime datetime,

    @out_username nvarchar(50),
    @out_usertype smallint,

    @out_nickname nvarchar(20),
    @out_signature nvarchar(255),
    @out_actualname nvarchar(20),
    @out_sex smallint,
    @out_age smallint,
    @out_birth_year smallint,
    @out_birth_month smallint,
    @out_birth_day smallint,
    @out_country smallint,
    @out_province smallint,
    @out_city smallint,
    @out_area smallint,
    @out_address nvarchar(50),
    @out_telephone nvarchar(20),
    @out_telephone_short nvarchar(20),
    @out_telephone2 nvarchar(20),
    @out_telephone2_short nvarchar(20),
    @out_mobile nvarchar(20),
    @out_mobile_short nvarchar(20),
    @out_mobile2 nvarchar(20),
    @out_mobile2_short nvarchar(20),
    @out_fax nvarchar(20),
    @out_qq nvarchar(50),
    @out_weixin nvarchar(50),
    @out_weibo nvarchar(50),
    @out_email nvarchar(50),
    @out_roomname nvarchar(50),
    @out_roomnumber nvarchar(50),
    @out_idnumber nvarchar(50),
    @out_carnumber nvarchar(50),
    @out_homepage nvarchar(80),
    @out_departid int,
    @out_departname nvarchar(50),
    @out_jobtitle nvarchar(50),
    @out_jobnumber nvarchar(20),
    @out_introduction nvarchar(255),

    @out_facefile nvarchar(255),
    @out_photofile nvarchar(255),
    
    @out_loginstatus smallint)
*/
as
begin
  select a.MEMBERID as out_memberid, a.ROOMID as out_roomid, a.ISMANAGER as out_ismanager, a.LASTRECVMSGSID as out_lastrecvmsgsid,
        a.MSGHINTSETTING as out_msghintsetting, a.MODIFYCARDBYMNG as out_modifycardbymng, a.CARD_NAME as out_card_name, 
	a.CARD_SEX as out_card_sex, a.CARD_NUMBER as out_card_number, a.CARD_EMAIL as out_card_email, a.CARD_REMARK as out_card_remark, 
	a.JOINTIME as out_jointime, b.USERNAME as out_username, b.USERTYPE as out_usertype, b.NICKNAME as out_nickname, 
	b.SIGNATURE as out_signature, b.ACTUALNAME as out_actualname, b.SEX as out_sex, b.AGE as out_age, b.BIRTH_YEAR as out_birth_year, 
	b.BIRTH_MONTH as out_birth_month, b.BIRTH_DAY as out_birth_day, b.COUNTRY as out_country, b.PROVINCE as out_province, 
	b.CITY as out_city, b.AREA as out_area, b.ADDRESS as out_address, b.TELEPHONE as out_telephone, 
	b.TELEPHONE_SHORT as out_telephone_short, b.TELEPHONE2 as out_telephone2, 
       b.TELEPHONE2_SHORT as out_telephone2_short, b.MOBILE as out_mobile, b.MOBILE_SHORT as out_mobile_short, 
       b.MOBILE2 as out_mobile2, b.MOBILE2_SHORT as out_mobile2_short, b.FAX as out_fax, b.QQ as out_qq, b.WEIXIN as out_weixin, 
       b.WEIBO as out_weibo, b.EMAIL as out_email, b.ROOMNAME as out_roomname, b.ROOMNUMBER as out_roomnumber, b.IDNUMBER as out_idnumber, 
       b.CARNUMBER as out_carnumber, b.HOMEPAGE as out_homepage, b.DEPARTID as out_departid, b.DEPARTNAME as out_departname, 
       b.JOBTITLE as out_jobtitle, b.JOBNUMBER as out_jobnumber, b.INTRODUCTION as out_introduction, 
       b.FACEFILE as out_facefile, b.PHOTOFILE as out_photofile, b.LOGINSTATUS as out_loginstatus
	from ROOMMEMBERS as a inner join USERS as b on a.MEMBERID = b.USERID where a.ROOMID = @in_roomid;
end;
GO

/* ADDROOMSHAREFILE */

CREATE PROCEDURE ADDROOMSHAREFILE 
    @in_sid varchar(50),
    @in_memberid int,
    @in_roomid int,
    @in_filenameordir nvarchar(255),
    @in_isdir smallint,
    @in_allfilecount int,
    @in_allfilesize bigint
as
begin
  SET NOCOUNT ON;

  insert into ROOMSHAREFILES (
	SID,
	MEMBERID,
	ROOMID,
	FILENAMEORDIR,
	ISDIR,
        ALLFILECOUNT,
	ALLFILESIZE,
	CREATETIME
    )
    values(
        @in_sid,
	@in_memberid,
	@in_roomid,
	@in_filenameordir,
	@in_isdir,
	@in_allfilecount,
	@in_allfilesize,
	getdate()
    );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEROOMSHAREFILE */

CREATE PROCEDURE REMOVEROOMSHAREFILE 
    @in_roomid int,
    @in_sid varchar(50)
as
begin
  SET NOCOUNT ON;

  delete from ROOMSHAREFILES where SID = @in_sid and ROOMID = @in_roomid;

  SET NOCOUNT OFF;
end;
GO

/* GETROOMSHAREFILES */

CREATE PROCEDURE GETROOMSHAREFILES 
    @in_roomid int
/*
returns (
    @out_sid varchar(50),
    @out_memberid int,
    @out_member_nickname nvarchar(20),
    @out_member_actualname nvarchar(20),
    @out_roomid int,
    @out_filenameordir nvarchar(255),
    @out_isdir smallint,
    @out_allfilecount int,
    @out_allfilesize bigint,
    @out_createtime datetime)
*/
as
begin
  select a.SID as out_sid, a.MEMBERID as out_memberid, b.NICKNAME as out_member_nickname, b.ACTUALNAME as out_member_actualname, 
        a.ROOMID as out_roomid, a.FILENAMEORDIR as out_filenameordir, a.ISDIR as out_isdir, a.ALLFILECOUNT as out_allfilecount, 
	a.ALLFILESIZE as out_allfilesize, a.CREATETIME as out_createtime 
	from ROOMSHAREFILES as a inner join USERS as b on a.MEMBERID = b.USERID 
	where a.ROOMID = @in_roomid;
end;
GO

/* ADDJOINROOMREQUEST */

CREATE PROCEDURE ADDJOINROOMREQUEST 
    @in_userid int,
    @in_roomid int,
    @in_content nvarchar(255),
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINROOMREQUESTS 
	where USERID = @in_userid and ROOMID = @in_roomid and SENDER = @in_sender)) 
  begin
    update JOINROOMREQUESTS set CONTENT = @in_content 
      where USERID = @in_userid and ROOMID = @in_roomid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINROOMREQUESTS(USERID, ROOMID, CONTENT, SENDER, SENDTIME) 
      values(@in_userid, @in_roomid, @in_content, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINROOMREQUESTS */

CREATE PROCEDURE GETJOINROOMREQUESTS 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_roomid int,
    @out_content nvarchar(255),
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, ROOMID as out_roomid, CONTENT as out_content, SENDER as out_sender, SENDTIME as out_sendtime
	from JOINROOMREQUESTS where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINROOMREQUESTS where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDJOINROOMRESPONSE */

CREATE PROCEDURE ADDJOINROOMRESPONSE 
    @in_userid int,
    @in_roomid int,
    @in_content nvarchar(255),
    @in_agree smallint,
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINROOMRESPONSES 
	where USERID = @in_userid and ROOMID = @in_roomid and SENDER = @in_sender)) 
  begin
    update JOINROOMRESPONSES set CONTENT = @in_content, AGREE = @in_agree 
      where USERID = @in_userid and ROOMID = @in_roomid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINROOMRESPONSES(USERID, ROOMID, CONTENT, AGREE, SENDER, SENDTIME) 
      values(@in_userid, @in_roomid, @in_content, @in_agree, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINROOMRESPONSES */

CREATE PROCEDURE GETJOINROOMRESPONSES 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_roomid int,
    @out_content nvarchar(255),
    @out_agree smallint,
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, ROOMID as out_roomid, CONTENT, 
        AGREE as out_agree, SENDER as out_sender, SENDTIME as out_sendtime
	from JOINROOMRESPONSES where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINROOMRESPONSES where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDOFFROOMMSG */

CREATE PROCEDURE ADDOFFROOMMSG 
    @in_roomid int,
    @in_msgid varchar(50),
    @in_msglevel smallint,
    @in_content nvarchar(4000),
    @in_font nvarchar(50),
    @in_sender_userid int,
    @in_sender_nickname nvarchar(20),
    @in_sender_actualname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  delete from OFFROOMMSGS where ROOMID = @in_roomid and SENDTIME > dateadd(day, 7, getdate());

  insert into OFFROOMMSGS(
    ROOMID,
    MSGID,
    MSGLEVEL,
    CONTENT,
    FONT,
    SENDER_USERID,
    SENDER_NICKNAME,
    SENDER_ACTUALNAME,
    SENDTIME
  ) values (
    @in_roomid,
    @in_msgid,
    @in_msglevel,
    @in_content,
    @in_font,
    @in_sender_userid,
    @in_sender_nickname,
    @in_sender_actualname,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFROOMMSG */

CREATE PROCEDURE REMOVEOFFROOMMSG 
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from OFFROOMMSGS where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFROOMMSGS */

CREATE PROCEDURE GETOFFROOMMSGS 
    @in_roomid int,
    @in_lastread_sid bigint
/*
returns (
    @out_sid bigint,
    @out_roomid int,
    @out_msgid varchar(50),
    @out_msglevel smallint,
    @out_content nvarchar(8192),
    @out_font nvarchar(50),
    @out_sender_userid int,
    @out_sender_nickname nvarchar(20),
    @out_sender_actualname nvarchar(20),
    @out_sendtime datetime)
*/
as
begin
  select top 200 SID as out_sid, ROOMID as out_roomid, MSGID as out_msgid, MSGLEVEL as out_msglevel, 
      CONTENT as out_content, FONT as out_font, SENDER_USERID as out_sender_userid, SENDER_NICKNAME as out_sender_nickname, 
      SENDER_ACTUALNAME as out_sender_actualname, SENDTIME as out_sendtime 
      from OFFROOMMSGS where ROOMID = @in_roomid and SID > @in_lastread_sid order by SID ASC;
end;
GO

/* ADDOFFROOMSUPEROBJ */

CREATE PROCEDURE ADDOFFROOMSUPEROBJ 
    @in_roomid int,
    @in_msgid varchar(50),
    @in_fromuserid int,
    @in_superobjectcode nvarchar(1024),
    @in_localfilename nvarchar(1024)
as
begin
  SET NOCOUNT ON;

  delete from OFFROOMSUPEROBJS where ROOMID = @in_roomid and CREATETIME > dateadd(day, 7, getdate());

  insert into OFFROOMSUPEROBJS(
    ROOMID,
    MSGID,
    FROMUSERID,
    SUPEROBJCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    @in_roomid,
    @in_msgid,
    @in_fromuserid,
    @in_superobjectcode,
    @in_localfilename,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFROOMSUPEROBJ */

CREATE PROCEDURE REMOVEOFFROOMSUPEROBJ 
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from OFFROOMSUPEROBJS where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFROOMSUPEROBJS */

CREATE PROCEDURE GETOFFROOMSUPEROBJS 
    @in_roomid int,
    @in_msgid varchar(50),
    @in_fromuserid int
/*
returns (
    @out_sid bigint,
    @out_roomid int,
    @out_msgid nvarchar(50),
    @out_fromuserid int,
    @out_superobjectcode nvarchar(1024),
    @out_localfilename nvarchar(1024),
    @out_createtime datetime)
*/
as
begin
  select SID as out_sid, ROOMID as out_roomid, MSGID as out_msgid, FROMUSERID as out_fromuserid, 
    SUPEROBJCODE as out_superobjectcode, LOCALFILENAME as out_localfilename, CREATETIME as out_createtime
    from OFFROOMSUPEROBJS where ROOMID = @in_roomid and MSGID = @in_msgid and FROMUSERID = @in_fromuserid;
end;
GO

/* GETWEBVISITORID */

CREATE PROCEDURE GETWEBVISITORID 
    @in_visitorcode nvarchar(50)
/*
returns (
    @out_visitorid int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_visitorid int;

  set @out_visitorid = 0;

  if (@in_visitorcode != '') 
  begin
    select top 1 @out_visitorid = VISITORID from WEBVISITORS where VISITORCODE = @in_visitorcode;
  end

  if ((@out_visitorid is null) or (@out_visitorid = 0)) 
  begin
    update WEBVISITORTOTAL set KEYVALUE = KEYVALUE + 1 where KEYNAME = 'VISITORID';
    select @out_visitorid = KEYVALUE from WEBVISITORTOTAL where KEYNAME = 'VISITORID';
  end

  SET NOCOUNT OFF;

  select @out_visitorid as out_visitorid;
end;
GO

/* ADDWEBVISITOR */

CREATE PROCEDURE ADDWEBVISITOR 
    @in_visitorid int,
    @in_visitorcode nvarchar(50),
    @in_userid int,
    @in_srcurl	nvarchar(1024),
    @in_clientipaddr nvarchar(50),
    @in_clientlocation nvarchar(50),
    @in_clientos nvarchar(50),
    @in_webbrowser nvarchar(50),
    @in_createtime datetime
/*
returns (
    @out_visitorid int,
    @out_visitorcode nvarchar(50),
    @out_userid int,
    @out_srcurl nvarchar(1024),
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_clientos nvarchar(50),
    @out_webbrowser nvarchar(50),
    @out_remarkname nvarchar(20),
    @out_remarktext nvarchar(1024),
    @out_flag smallint,
    @out_isactive smallint,
    @out_logintimes int,
    @out_lastlogintime datetime,
    @out_sendmsgs int,
    @out_recvmsgs int,
    @out_leavemsgs int,
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  if (exists(select * from WEBVISITORS where VISITORID = @in_visitorid and USERID = @in_userid)) 
  begin
    update WEBVISITORS set VISITORCODE = @in_visitorcode, SRCURL = @in_srcurl, 
	CLIENTIPADDR = @in_clientipaddr, CLIENTLOCATION = @in_clientlocation, CLIENTOS = @in_clientos, 
	WEBBROWSER = @in_webbrowser, ISACTIVE = 1, LOGINTIMES = LOGINTIMES + 1, LASTLOGINTIME = @in_createtime
	where VISITORID = @in_visitorid and USERID = @in_userid;
  end
  else 
  begin
    insert into WEBVISITORS(VISITORID, VISITORCODE, USERID, SRCURL, CLIENTIPADDR, CLIENTLOCATION, 
	CLIENTOS, WEBBROWSER, REMARKNAME, REMARKTEXT, FLAG, ISACTIVE, LOGINTIMES, LASTLOGINTIME, 
	SENDMSGS, RECVMSGS, LEAVEMSGS, CREATETIME) values(@in_visitorid, @in_visitorcode, @in_userid, 
        @in_srcurl, @in_clientipaddr, @in_clientlocation, @in_clientos, @in_webbrowser, '', '', 
	0, 1, 1, @in_createtime, 0, 0, 0, @in_createtime);
  end

  SET NOCOUNT OFF;

  select VISITORID as out_visitorid, VISITORCODE as out_visitorcode, USERID as out_userid, SRCURL as out_srcurl, 
        CLIENTIPADDR as out_clientipaddr, CLIENTLOCATION as out_clientlocation, CLIENTOS as out_clientos, 
	WEBBROWSER as out_webbrowser, REMARKNAME as out_remarkname, REMARKTEXT as out_remarktext, FLAG as out_flag, 
	ISACTIVE as out_isactive, LOGINTIMES as out_logintimes, LASTLOGINTIME as out_lastlogintime, 
	SENDMSGS as out_sendmsgs, RECVMSGS as out_recvmsgs, LEAVEMSGS as out_leavemsgs, CREATETIME as out_createtime 
	from WEBVISITORS where (VISITORID = @in_visitorid) and (USERID = @in_userid);
end;
GO

/* REMOVEWEBVISITOR */

CREATE PROCEDURE REMOVEWEBVISITOR 
    @in_visitorid int,
    @in_userid int
as
begin
  SET NOCOUNT ON;

  delete from WEBVISITORS where (VISITORID = @in_visitorid) and (USERID = @in_userid) and (ISACTIVE = 0);

  SET NOCOUNT OFF;
end;
GO

/* CHANGEWEBVISITORREMARKNAME */

CREATE PROCEDURE CHANGEWEBVISITORREMARKNAME 
    @in_visitorid int,
    @in_remarkname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set REMARKNAME = @in_remarkname where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

CREATE PROCEDURE CHANGEWEBVISITORREMARKTEXT 
    @in_visitorid int,
    @in_remarktext nvarchar(1024)
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set REMARKTEXT = @in_remarktext where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

/* WEBVISITORINACTIVE */

CREATE PROCEDURE WEBVISITORINACTIVE 
    @in_visitorid int
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set ISACTIVE = 0 where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

/* WEBVISITORINCSENDMSGS */

CREATE PROCEDURE WEBVISITORINCSENDMSGS 
    @in_visitorid int
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set SENDMSGS = SENDMSGS + 1 where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

/* WEBVISITORINCRECVMSGS */

CREATE PROCEDURE WEBVISITORINCRECVMSGS 
    @in_visitorid int
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set RECVMSGS = RECVMSGS + 1 where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

/* WEBVISITORINCLEAVEMSGS */

CREATE PROCEDURE WEBVISITORINCLEAVEMSGS 
    @in_visitorid int
as
begin
  SET NOCOUNT ON;

  update WEBVISITORS set LEAVEMSGS = LEAVEMSGS + 1 where VISITORID = @in_visitorid;

  SET NOCOUNT OFF;
end;
GO

/* GETINACTIVEWEBVISITORS */

CREATE PROCEDURE GETINACTIVEWEBVISITORS 
    @in_userid int
/*
returns (
    @out_visitorid int,
    @out_visitorcode nvarchar(50),
    @out_userid int,
    @out_srcurl nvarchar(1024),
    @out_clientipaddr nvarchar(50),
    @out_clientlocation nvarchar(50),
    @out_clientos nvarchar(50),
    @out_webbrowser nvarchar(50),
    @out_remarkname nvarchar(20),
    @out_remarktext nvarchar(1024),
    @out_flag smallint,
    @out_isactive smallint,
    @out_logintimes int,
    @out_lastlogintime datetime,
    @out_sendmsgs int,
    @out_recvmsgs int,
    @out_leavemsgs int,
    @out_createtime datetime)
*/
as
begin
  select top 100 VISITORID as out_visitorid, VISITORCODE as out_visitorcode, USERID as out_userid, SRCURL as out_srcurl, 
        CLIENTIPADDR as out_clientipaddr, CLIENTLOCATION as out_clientlocation, CLIENTOS as out_clientos, 
        WEBBROWSER as out_webbrowser, REMARKNAME as out_remarkname, REMARKTEXT as out_remarktext, FLAG as out_flag, 
	ISACTIVE as out_isactive, LOGINTIMES as out_logintimes, LASTLOGINTIME as out_lastlogintime, 
	SENDMSGS as out_sendmsgs, RECVMSGS as out_recvmsgs, LEAVEMSGS as out_leavemsgs, CREATETIME as out_createtime  
	from WEBVISITORS where (USERID = @in_userid) and (ISACTIVE = 0) order by VISITORID DESC;
end;
GO

/* ADDWEBVISITORMESSAGE */

CREATE PROCEDURE ADDWEBVISITORMESSAGE 
    @in_visitorid int,
    @in_userid int,
    @in_senderisvisitor smallint,
    @in_content nvarchar(1024),
    @in_createtime datetime
as
begin
  SET NOCOUNT ON;

  insert into WEBVISITORMESSAGES(VISITORID, USERID, SENDERISVISITOR, CONTENT, CREATETIME) 
      values(@in_visitorid, @in_userid, @in_senderisvisitor, @in_content, @in_createtime);

  SET NOCOUNT OFF;
end;
GO

/* GETRECENTWEBVISITORMESSAGES */

CREATE PROCEDURE GETRECENTWEBVISITORMESSAGES 
    @in_visitorid int,
    @in_userid int,
    @in_recordcount int
/*
returns (
    @out_visitorid int,
    @out_userid int,
    @out_senderisvisitor smallint,
    @out_content nvarchar(1024),
    @out_createtime datetime)
*/
as
begin
  declare @var_sql nvarchar(4000);

  set @var_sql = 'select top ' + str(@in_recordcount) + ' VISITORID as out_visitorid, USERID as out_userid,' +
       ' SENDERISVISITOR as out_senderisvisitor, CONTENT as out_content, CREATETIME as out_createtime' +
       ' from WEBVISITORMESSAGES where (VISITORID = @in_visitorid) and (USERID = @in_userid) order by SID DESC';

  exec(@var_sql);
end;
GO

/* GETWEBVISITORMESSAGES */

CREATE PROCEDURE GETWEBVISITORMESSAGES 
    @in_visitorid int,
    @in_userid int,
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_maxpageindex int,
    @out_visitorid int,
    @out_userid int,
    @out_senderisvisitor smallint,
    @out_content nvarchar(1024),
    @out_createtime datetime)
*/
as
begin
  declare @out_maxpageindex int;

  declare @var_messagecount int;
  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(4000);
  declare @var_paging_sql nvarchar(4000);

  if (@in_pagesize <= 0)  set @in_pagesize = 40;

  select @var_messagecount = count(*) from WEBVISITORMESSAGES where (VISITORID = @in_visitorid) and (USERID = @in_userid);
  set @out_maxpageindex = @var_messagecount / @in_pagesize;
  if ((@var_messagecount % @in_pagesize) > 0) 
  begin
    set @out_maxpageindex = @out_maxpageindex + 1;
  end

  if (@out_maxpageindex <= 0) set @out_maxpageindex = 1;

  if ((@in_pageindex > @out_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @out_maxpageindex;
  end

  set @var_where_sql = '(VISITORID = ' + cast(@in_visitorid as nvarchar(10)) + ') and (USERID = ' +
        cast(@in_userid as nvarchar(10)) + ') order by SID ASC';

  set @var_fields_sql = 
      str(@out_maxpageindex) + ' as out_maxpageindex, VISITORID as out_visitorid,' +
      ' USERID as out_userid, SENDERISVISITOR as out_senderisvisitor, CONTENT as out_content, CREATETIME as out_createtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from WEBVISITORMESSAGES where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from WEBVISITORMESSAGES where ' +
          ' (VISITORID > (select max(VISITORID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' VISITORID from WEBVISITORMESSAGES where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  exec(@var_paging_sql);
end;
GO

/* ADDWEBAPP */

CREATE PROCEDURE ADDWEBAPP 
    @in_foruserid int,
    @in_forusertype smallint,
    @in_appname nvarchar(50),
    @in_category nvarchar(50),
    @in_introduction nvarchar(255),
    @in_appimage nvarchar(255),
    @in_appurl nvarchar(1024),
    @in_method smallint,
    @in_postdata nvarchar(1024),
    @in_popup int,
    @in_clientwebbrowser smallint,
    @in_webbrowserwidth int,
    @in_webbrowserheight int,
    @in_shortcut smallint
/*
returns (
    out_appid int,
    out_foruserid int,
    out_forusertype smallint,
    out_appname nvarchar(50),
    out_category nvarchar(50),
    out_introduction nvarchar(255),
    out_appimage nvarchar(255),
    out_appurl nvarchar(1024),
    out_method smallint,
    out_postdata nvarchar(1024),
    out_popup int,
    out_clientwebbrowser smallint,
    out_webbrowserwidth int,
    out_webbrowserheight int,
    out_shortcut smallint,
    out_ordervalue int,
    out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

   declare @var_newappid int;
   declare @var_ordervalue int;

  select @var_newappid = max(APPID) from WEBAPPS;
  if (@var_newappid is null) set @var_newappid = 0;
  set @var_newappid = @var_newappid + 1;

  select @var_ordervalue = max(ORDERVALUE) from WEBAPPS;
  if (@var_ordervalue is null) set @var_ordervalue = 0;
  set @var_ordervalue = @var_ordervalue + 1;

  insert into WEBAPPS(
    APPID,
    FORUSERID,
    FORUSERTYPE,
    APPNAME,
    CATEGORY,            
    INTRODUCTION,
    APPIMAGE,
    APPURL,
    METHOD,
    POSTDATA,
    POPUP,
    CLIENTWEBBROWSER,
    WEBBROWSERWIDTH,
    WEBBROWSERHEIGHT,
    SHORTCUT,
    ORDERVALUE,
    CREATETIME
    )
    values(
    @var_newappid,
    @in_foruserid,
    @in_forusertype,
    @in_appname,
    @in_category,
    @in_introduction,
    @in_appimage,
    @in_appurl,
    @in_method,
    @in_postdata,
    @in_popup,
    @in_clientwebbrowser,
    @in_webbrowserwidth,
    @in_webbrowserheight,
    @in_shortcut,
    @var_ordervalue,
    getdate()
    );

  SET NOCOUNT OFF;

  select APPID as out_appid, FORUSERID as out_foruserid, FORUSERTYPE as out_forusertype, APPNAME as out_appname, CATEGORY as out_category, INTRODUCTION as out_introduction, 
       APPIMAGE as out_appimage, APPURL as out_appurl, METHOD as out_method, POSTDATA as out_postdata, POPUP as out_popup, CLIENTWEBBROWSER as out_clientwebbrowser, 
       WEBBROWSERWIDTH as out_webbrowserwidth, WEBBROWSERHEIGHT as out_webbrowserheight, SHORTCUT as out_shortcut, ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
       from WEBAPPS where APPID = @var_newappid;
end;
GO

/* REMOVEWEBAPP */

CREATE PROCEDURE REMOVEWEBAPP 
    @in_appid int
/*
returns (
    @out_appimage nvarchar(255))
*/
as
begin
  SET NOCOUNT ON;

  declare @out_appimage nvarchar(255);

  select @out_appimage = APPIMAGE from WEBAPPS where APPID = @in_appid;
  delete from WEBAPPS where APPID = @in_appid;

  SET NOCOUNT OFF;

  select @out_appimage as out_appimage;
end;
GO

/* MODIFYWEBAPP */

CREATE PROCEDURE MODIFYWEBAPP 
    @in_appid int,
    @in_appname nvarchar(50),
    @in_category nvarchar(50),
    @in_introduction nvarchar(255),
    @in_appurl nvarchar(1024),
    @in_method smallint,
    @in_postdata nvarchar(1024),
    @in_popup int,
    @in_clientwebbrowser smallint,
    @in_webbrowserwidth int,
    @in_webbrowserheight int,
    @in_shortcut smallint
as
begin
  SET NOCOUNT ON;

  update WEBAPPS set APPNAME = @in_appname, CATEGORY = @in_category, INTRODUCTION = @in_introduction, 
       APPURL = @in_appurl, METHOD = @in_method, POSTDATA = @in_postdata, POPUP = @in_popup,
       CLIENTWEBBROWSER = @in_clientwebbrowser, WEBBROWSERWIDTH = @in_webbrowserwidth, WEBBROWSERHEIGHT = @in_webbrowserheight,
       SHORTCUT = @in_shortcut where APPID = @in_appid;

  SET NOCOUNT OFF;
end;
GO

/* CHANGEWEBAPPIMAGE */

CREATE PROCEDURE CHANGEWEBAPPIMAGE 
    @in_appid int,
    @in_appimage nvarchar(255)
/*
returns (
    @out_appimage nvarchar(255))
*/
as
begin
  SET NOCOUNT ON;

  declare @out_appimage nvarchar(255);

  select @out_appimage = APPIMAGE from WEBAPPS where APPID = @in_appid;
  update WEBAPPS set APPIMAGE = @in_appimage where APPID = @in_appid;

  SET NOCOUNT OFF;

  select @out_appimage as out_appimage;
end;
GO

/* CHANGEWEBAPPORDERVALUE */

CREATE PROCEDURE CHANGEWEBAPPORDERVALUE 
    @in_appid int,
    @in_ordervalue int
as
begin
  SET NOCOUNT ON;

  update WEBAPPS set ORDERVALUE = @in_ordervalue where APPID = @in_appid;

  SET NOCOUNT OFF;
end;
GO

/* GETWEBAPP */

CREATE PROCEDURE GETWEBAPP 
    @in_appid int
/*
returns (
    out_appid int,
    out_foruserid int,
    out_forusertype smallint,
    out_appname nvarchar(50),
    out_category nvarchar(50),
    out_introduction nvarchar(255),
    out_appimage nvarchar(255),
    out_appurl nvarchar(1024),
    out_method smallint,
    out_postdata nvarchar(1024),
    out_popup int,
    out_clientwebbrowser smallint,
    out_webbrowserwidth int,
    out_webbrowserheight int,
    out_shortcut smallint,
    out_ordervalue int,
    out_createtime datetime)
*/
as
begin
    select APPID as out_appid, FORUSERID as out_foruserid, FORUSERTYPE as out_forusertype, APPNAME as out_appname, 
       CATEGORY as out_category, INTRODUCTION as out_introduction, APPIMAGE as out_appimage, APPURL as out_appurl, 
       METHOD as out_method, POSTDATA as out_postdata, POPUP as out_popup,
       CLIENTWEBBROWSER as out_clientwebbrowser, WEBBROWSERWIDTH as out_webbrowserwidth, WEBBROWSERHEIGHT as out_webbrowserheight, 
       SHORTCUT as out_shortcut, ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
       from WEBAPPS where APPID = @in_appid;
end;
GO

/* GETUSERWEBAPPS */

CREATE PROCEDURE GETUSERWEBAPPS 
    @in_userid int,
    @in_usertype smallint
/*
returns (
    @out_appid int,
    @out_foruserid int,
    @out_forusertype smallint,
    @out_appname nvarchar(50),
    @out_category nvarchar(50),
    @out_introduction nvarchar(255),
    @out_appimage nvarchar(255),
    @out_appurl nvarchar(1024),
    @out_method smallint,
    @out_postdata nvarchar(1024),
    @out_popup int,
    @out_clientwebbrowser smallint,
    @out_webbrowserwidth int,
    @out_webbrowserheight int,
    @out_shortcut smallint,
    @out_ordervalue int,
    @out_createtime datetime)
*/
as
begin
  if (@in_userid > 0)
  begin
	  select APPID as out_appid, FORUSERID as out_foruserid, FORUSERTYPE as out_forusertype, APPNAME as out_appname, 
	       CATEGORY as out_category, INTRODUCTION as out_introduction, APPIMAGE as out_appimage, APPURL as out_appurl, 
	       METHOD as out_method, POSTDATA as out_postdata, POPUP as out_popup,
	       CLIENTWEBBROWSER as out_clientwebbrowser, WEBBROWSERWIDTH as out_webbrowserwidth, WEBBROWSERHEIGHT as out_webbrowserheight, 
	       SHORTCUT as out_shortcut, ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
	       from WEBAPPS where (FORUSERID = @in_userid) or (FORUSERTYPE = @in_usertype) order by ORDERVALUE;
  end
  else
  begin
	  select APPID as out_appid, FORUSERID as out_foruserid, FORUSERTYPE as out_forusertype, APPNAME as out_appname, 
	       CATEGORY as out_category, INTRODUCTION as out_introduction, APPIMAGE as out_appimage, APPURL as out_appurl, 
	       METHOD as out_method, POSTDATA as out_postdata, POPUP as out_popup,
	       CLIENTWEBBROWSER as out_clientwebbrowser, WEBBROWSERWIDTH as out_webbrowserwidth, WEBBROWSERHEIGHT as out_webbrowserheight, 
	       SHORTCUT as out_shortcut, ORDERVALUE as out_ordervalue, CREATETIME as out_createtime
	       from WEBAPPS where (FORUSERID = 0) and (FORUSERTYPE = @in_usertype) order by ORDERVALUE;

  end
end;
GO

/* ADDRECENTITEM */

CREATE PROCEDURE ADDRECENTITEM 
    @in_userid int,
    @in_itemtype smallint,
    @in_itemvalue nvarchar(255)
/*
returns (
    @out_sid bigint,
    @out_userid int,
    @out_itemtype smallint,
    @out_itemvalue nvarchar(255),
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  if (exists(select * from RECENTITEMS where (USERID = @in_userid) and (ITEMTYPE = @in_itemtype) and (ITEMVALUE = @in_itemvalue))) 
  begin
    update RECENTITEMS set CREATETIME = getdate() where (USERID = @in_userid) and (ITEMTYPE = @in_itemtype) and (ITEMVALUE = @in_itemvalue);
  end
  else
  begin
    insert into RECENTITEMS(
      USERID,
      ITEMTYPE,
      ITEMVALUE,
      CREATETIME
      )
      values(
      @in_userid,
      @in_itemtype,
      @in_itemvalue,
      getdate()
      );
  end

  SET NOCOUNT OFF;

  select SID as out_sid, USERID as out_userid, ITEMTYPE as out_itemtype, ITEMVALUE as out_itemvalue, CREATETIME as out_createtime
       from RECENTITEMS where (USERID = @in_userid) and (ITEMTYPE = @in_itemtype) and (ITEMVALUE = @in_itemvalue);
end;
GO

/* REMOVERECENTITEM */

CREATE PROCEDURE REMOVERECENTITEM 
    @in_userid int,
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from RECENTITEMS where SID = @in_sid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETRECENTITEMS */

CREATE PROCEDURE GETRECENTITEMS 
    @in_userid int
/*
returns (
    @out_sid bigint,
    @out_userid int,
    @out_itemtype smallint,
    @out_itemvalue nvarchar(255),
    @out_createtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, ITEMTYPE as out_itemtype, ITEMVALUE as out_itemvalue, CREATETIME as out_createtime
       from RECENTITEMS where USERID = @in_userid order by CREATETIME DESC;
end;
GO

/* ADDOFFLINEFILES */

CREATE PROCEDURE ADDOFFLINEFILES 
    @in_sid varchar(50),
    @in_sender_userid int,
    @in_receiver_userid int,
    @in_filenameordir nvarchar(255),
    @in_isdir smallint,
    @in_allfilecount int,
    @in_allfilesize bigint
as
begin
  SET NOCOUNT ON;

  insert into OFFLINEFILES (
	SID,
	SENDER_USERID,
	RECEIVER_USERID,
	FILENAMEORDIR,
	ISDIR,
        ALLFILECOUNT,
	ALLFILESIZE,
	CREATETIME
    )
    values(
        @in_sid,
	@in_sender_userid,
	@in_receiver_userid,
	@in_filenameordir,
	@in_isdir,
	@in_allfilecount,
	@in_allfilesize,
	getdate()
    );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFLINEFILES */

CREATE PROCEDURE REMOVEOFFLINEFILES 
    @in_sid varchar(50)
as
begin
  SET NOCOUNT ON;

  delete from OFFLINEFILES where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFLINEFILES */

CREATE PROCEDURE GETOFFLINEFILES 
    @in_userid int
/*
returns (
    @out_sid varchar(50),
    @out_sender_userid int,
    @out_receiver_userid int,
    @out_filenameordir nvarchar(255),
    @out_isdir smallint,
    @out_allfilecount int,
    @out_allfilesize bigint,
    @out_createtime datetime)
*/
as
begin
  select SID as out_sid, SENDER_USERID as out_sender_userid, RECEIVER_USERID as out_receiver_userid, FILENAMEORDIR as out_filenameordir, 
	ISDIR as out_isdir, ALLFILECOUNT as out_allfilecount, ALLFILESIZE as out_allfilesize, CREATETIME as out_createtime 
	from OFFLINEFILES where RECEIVER_USERID = @in_userid;
end;
GO

/* ADDSYSMSG */

CREATE PROCEDURE ADDSYSMSG 
    @in_title nvarchar(50),
    @in_category nvarchar(50),
    @in_content nvarchar(1024),
    @in_link nvarchar(255),
    @in_attachfile nvarchar(255),
    @in_forusertype smallint,
    @in_fordepartid int, 
    @in_fordepartname nvarchar(50),
    @in_sendtonum int,
    @in_sendtodetail nvarchar(255),
    @in_foronlineusers smallint,
    @in_needreaded smallint,
    @in_needfeedback smallint,
    @in_sendtime datetime
/*
returns(
    @out_sysmsgid bigint)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_datebefore datetime;
  declare @var_sysmsgid int;

  set @var_datebefore = dateadd(day, -30, getdate());
  delete from SYSMSGDETAILS where SENDTIME = @var_datebefore;
  delete from SYSMSGS where SENDTIME < @var_datebefore;

  select @var_sysmsgid = max(SYSMSGID) from SYSMSGS;
  if (@var_sysmsgid is null)  set @var_sysmsgid = 0;
  set @var_sysmsgid = @var_sysmsgid + 1;

  insert into SYSMSGS(
    SYSMSGID, 
    TITLE, 
    CATEGORY,
    CONTENT, 
    LINK, 
    ATTACHFILE,
    FORUSERTYPE, 
    FORDEPARTID,
    FORDEPARTNAME,
    SENDTONUM,
    SENDTODETAIL,
    FORONLINEUSERS,
    NEEDREADED, 
    NEEDFEEDBACK, 
    READEDCOUNT,
    FEEDBACKCOUNT,
    SENDTIME
    ) values(
    @var_sysmsgid, 
    @in_title, 
    @in_category,
    @in_content, 
    @in_link, 
    @in_attachfile,
    @in_forusertype,
    @in_fordepartid,
    @in_fordepartname,
    @in_sendtonum,
    @in_sendtodetail,
    @in_foronlineusers,
    @in_needreaded, 
    @in_needfeedback, 
    0,
    0,
    @in_sendtime
    );

  SET NOCOUNT OFF;

  select @var_sysmsgid as out_sysmsgid;
end;
GO

/* GETUSERSYSMSGS */

CREATE PROCEDURE GETUSERSYSMSGS 
    @in_userid int
/*
returns(
    out_sysmsgid bigint,
    out_title nvarchar(50),
    out_category nvarchar(50),
    out_content nvarchar(1024),
    out_link nvarchar(255),
    out_attachfile nvarchar(255),
    out_forusertype smallint,
    out_fordepartid int, 
    out_fordepartname nvarchar(50),
    out_sendtonum int,
    out_sendtodetail nvarchar(255),
    out_foronlineusers smallint,
    out_needreaded smallint,
    out_needfeedback smallint,
    out_readedcount int,
    out_feedbackcount int,
    out_sendtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @var_usertype smallint;
  declare @var_departid int;
  declare @var_lastrecvsysmsgs datetime;

  select @var_usertype = USERTYPE, @var_departid = DEPARTID, @var_lastrecvsysmsgs = LASTRECVSYSMSGS 
      from USERS where USERID = @in_userid;

  if ((@var_usertype is null) or (@var_departid is null))
  begin
    return;
  end

  if (@var_lastrecvsysmsgs is null)
  begin
	set @var_lastrecvsysmsgs = dateadd(day, -30, getdate());
  end

  SET NOCOUNT OFF;

  select top 5 SYSMSGID as out_sysmsgid, TITLE as out_title, CATEGORY as out_category, CONTENT as out_content, LINK as out_link, ATTACHFILE as out_attachfile, 
	FORUSERTYPE as out_forusertype, FORDEPARTID as out_fordepartid, FORDEPARTNAME as out_fordepartname, SENDTONUM as out_sendtonum, SENDTODETAIL as out_sendtodetail, 
	FORONLINEUSERS as out_foronlineusers, NEEDREADED as out_needreaded, NEEDFEEDBACK as out_needfeedback, READEDCOUNT as out_readedcount, FEEDBACKCOUNT as out_feedbackcount, SENDTIME as out_sendtime
        from SYSMSGS where 
	(
	((FORUSERTYPE = @var_usertype) or (FORUSERTYPE = -1)) and 
	(FORONLINEUSERS = 0) and 
	(SENDTIME > @var_lastrecvsysmsgs)
	) 
	or
	(
	SYSMSGID in (
	    select distinct SYSMSGID from SYSMSGDETAILS where 
	    (SYSMSGDETAILS.USERID = @in_userid) and 
	    (SYSMSGDETAILS.SENDTIME > @var_lastrecvsysmsgs)
	)
	);

  SET NOCOUNT ON;

  update USERS set LASTRECVSYSMSGS = getdate() where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETSYSMSGS */

CREATE PROCEDURE GETSYSMSGS 
    @in_pagesize int,
    @in_pageindex int
/*
returns(
    out_maxpageindex int,
    out_sysmsgid bigint,
    out_title nvarchar(50),
    out_category nvarchar(50),
    out_content nvarchar(1024),
    out_link nvarchar(255),
    out_attachfile nvarchar(255),
    out_forusertype smallint,
    out_fordepartid int, 
    out_fordepartname nvarchar(50),
    out_sendtonum int,
    out_sendtodetail nvarchar(255),
    out_foronlineusers smallint,
    out_needreaded smallint,
    out_needfeedback smallint,
    out_readedcount int,
    out_feedbackcount int,
    out_sendtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_maxpageindex int;

  declare @var_sysmsgcount int;
  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(4000);
  declare @var_paging_sql nvarchar(4000);

  select @var_sysmsgcount = count(*) from SYSMSGS where 1 = 1;
  set @out_maxpageindex = @var_sysmsgcount / @in_pagesize;
  if ((@var_sysmsgcount % @in_pagesize) > 0) 
  begin
    set @out_maxpageindex = @out_maxpageindex + 1;
  end

  if (@out_maxpageindex <= 0) set @out_maxpageindex = 1;

  if ((@in_pageindex > @out_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @out_maxpageindex;
  end

  set @var_where_sql = '(1 = 1) order by SENDTIME DESC ';

  set @var_fields_sql = 
      str(@out_maxpageindex) + ' as out_maxpageindex, SYSMSGID as out_sysmsgid, TITLE as out_title, CATEGORY as out_category,' +
      ' CONTENT as out_content, LINK as out_link, ATTACHFILE as out_attachfile, FORUSERTYPE as out_forusertype, ' +
      ' FORDEPARTID as out_fordepartid, FORDEPARTNAME as out_fordepartname, SENDTONUM as out_sendtonum, ' +
      ' SENDTODETAIL as out_sendtodetail, FORONLINEUSERS as out_foronlineusers, NEEDREADED as out_needreaded, ' +
      ' NEEDFEEDBACK as out_needfeedback, READEDCOUNT as out_readedcount, FEEDBACKCOUNT as out_feedbackcount, SENDTIME as out_sendtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from SYSMSGS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from SYSMSGS where ' +
          ' (SYSMSGID < (select min(SYSMSGID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' SYSMSGID from SYSMSGS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  SET NOCOUNT OFF;

  exec(@var_paging_sql);
end;
GO

/* REMOVESYSMSG */

CREATE PROCEDURE REMOVESYSMSG 
    @in_sysmsgid bigint
as
begin
  SET NOCOUNT ON;

  delete from SYSMSGS where SYSMSGID = @in_sysmsgid;
  delete from SYSMSGDETAILS where SYSMSGID = @in_sysmsgid;
 
  SET NOCOUNT OFF;
end;
GO

/* ADDSYSMSGDETAIL */

CREATE PROCEDURE ADDSYSMSGDETAIL 
    @in_sysmsgid bigint, 
    @in_userid int,
    @in_readed smallint,
    @in_feedback smallint,
    @in_feedbacktext nvarchar(255),
    @in_sendtime datetime
as
begin
  SET NOCOUNT ON;

  insert into SYSMSGDETAILS(
    SYSMSGID, 
    USERID, 
    READED, 
    FEEDBACK, 
    FEEDBACKTEXT,
    SENDTIME
    ) values(
    @in_sysmsgid,
    @in_userid,
    @in_readed,
    @in_feedback,
    @in_feedbacktext,
    @in_sendtime
    );

  SET NOCOUNT OFF;
end;
GO

/* CHANGESYSMSGDETAIL_READED */

CREATE PROCEDURE CHANGESYSMSGDETAIL_READED
    @in_sysmsgid bigint, 
    @in_userid int
as
begin
  SET NOCOUNT ON;

  declare @var_sendtime datetime;

  if (not exists(select * from SYSMSGDETAILS where SYSMSGID = @in_sysmsgid and USERID = @in_userid))
  begin
	  select @var_sendtime = SENDTIME from SYSMSGS where SYSMSGID = @in_sysmsgid;
  
	  insert into SYSMSGDETAILS(
		SYSMSGID, 
		USERID, 
		READED, 
		FEEDBACK, 
		FEEDBACKTEXT,
		SENDTIME
		) values(
		@in_sysmsgid,
		@in_userid,
		1, /* readed */
		0, /* feedback */
		'', /* feedbacktext */
		@var_sendtime
		);
  end
  else
  begin
	update SYSMSGDETAILS set READED = 1
		where SYSMSGID = @in_sysmsgid and USERID = @in_userid;
  end

  SET NOCOUNT OFF;
end;
GO

/* CHANGESYSMSGDETAIL_FEEDBACK */

CREATE PROCEDURE CHANGESYSMSGDETAIL_FEEDBACK 
    @in_sysmsgid bigint, 
    @in_userid int,
    @in_feedbacktext nvarchar(255)
as
begin
  SET NOCOUNT ON;

  declare @var_sendtime datetime;

  if (not exists(select * from SYSMSGDETAILS where SYSMSGID = @in_sysmsgid and USERID = @in_userid))
  begin
	  select @var_sendtime = SENDTIME from SYSMSGS where SYSMSGID = @in_sysmsgid;
  
	  insert into SYSMSGDETAILS(
		SYSMSGID, 
		USERID, 
		READED, 
		FEEDBACK, 
		FEEDBACKTEXT,
		SENDTIME
		) values(
		@in_sysmsgid,
		@in_userid,
		1, /* readed */
		1, /* feedback */
		@in_feedbacktext, /* feedbacktext */
		@var_sendtime
		);
  end
  else
  begin
	update SYSMSGDETAILS set READED = 1, FEEDBACK = 1, FEEDBACKTEXT = @in_feedbacktext
		where SYSMSGID = @in_sysmsgid and USERID = @in_userid;
  end

  SET NOCOUNT OFF;
end;
GO

/* GETSYSMSGDETAILS */

CREATE PROCEDURE GETSYSMSGDETAILS 
    @in_sysmsgid bigint,
    @in_pagesize int,
    @in_pageindex int
/*
returns(
    out_maxpageindex int,
    out_sysmsgid bigint,
    out_userid int, 
    out_readed smallint,
    out_feedback smallint,
    out_feedbacktext varchar(255),
    out_sendtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_maxpageindex int;

  declare @var_itemcount int;
  declare @var_fields_sql nvarchar(4000);
  declare @var_where_sql nvarchar(4000);
  declare @var_paging_sql nvarchar(4000);

  select @var_itemcount = count(*) from SYSMSGDETAILS where SYSMSGID = @in_sysmsgid;
  set @out_maxpageindex = @var_itemcount / @in_pagesize;
  if ((@var_itemcount % @in_pagesize) > 0)
  begin
    set @out_maxpageindex = @out_maxpageindex + 1;
  end

  if ((@in_pageindex > @out_maxpageindex) or (@in_pageindex <= 0))
  begin
    set @in_pageindex = @out_maxpageindex;
  end

  set @var_where_sql = 'SYSMSGID = ' + cast(@in_sysmsgid as varchar(10)) + ' order by SENDTIME DESC ';

  set @var_fields_sql = 
      str(@out_maxpageindex) + ' as out_maxpageindex, SYSMSGID as out_sysmsgid, USERID as out_userid, ' +
      'READED as out_readed, FEEDBACK as out_feedback, FEEDBACKTEXT as out_feedbacktext, SENDTIME as out_sendtime';

  if (@in_pageindex = 1) 
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from SYSMSGDETAILS where ' + @var_where_sql;
  end
  else
  begin
    set @var_paging_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
      ' from SYSMSGDETAILS where ' +
          ' (USERID < (select min(USERID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' USERID from SYSMSGDETAILS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  SET NOCOUNT OFF;

  exec(@var_paging_sql);
end;
GO

/* ADDSENDSMS */

CREATE PROCEDURE ADDSENDSMS 
    @in_msgid varchar(50),
    @in_userid int,
    @in_mobile nvarchar(20),
    @in_content nvarchar(4000),
    @in_receiver int,
    @in_sendtime datetime
as
begin
  SET NOCOUNT ON;

  delete from SENDSMS where getdate() > dateadd(day, 1, SENDTIME);

  insert into SENDSMS(
    MSGID,
    USERID,
    MOBILE,
    CONTENT,
    RECEIVER,
    SENDTIME
    )
    values(
    @in_msgid,
    @in_userid,
    @in_mobile,
    @in_content,
    @in_receiver,
    @in_sendtime
    );

  SET NOCOUNT OFF;
end;
GO

/* GETSENDSMSLIST */

CREATE PROCEDURE GETSENDSMSLIST
    @in_userid int
/*
returns (
    out_msgid varchar(50),
    out_userid int,
    out_mobile varchar(20),
    out_content varchar(4000),
    out_receiver int,
    out_sendtime datetime)
*/
as
begin
  if (@in_userid > 0)
  begin
      select MSGID as out_msgid, USERID as out_userid, MOBILE as out_mobile, CONTENT as out_content, RECEIVER as out_receiver, SENDTIME as out_sendtime
        from SENDSMS where USERID = @in_userid and getdate() < dateadd(day, 1, SENDTIME);
  end
  else
  begin
      select MSGID as out_msgid, USERID as out_userid, MOBILE as out_mobile, CONTENT as out_content, RECEIVER as out_receiver, SENDTIME as out_sendtime
        from SENDSMS where getdate() < dateadd(day, 1, SENDTIME);
  end
end;
GO

/* ADDRECVSMS */

CREATE PROCEDURE ADDRECVSMS 
    @in_msgid varchar(50),
    @in_userid int,
    @in_mobile nvarchar(20),
    @in_content nvarchar(4000),
    @in_haveread smallint,
    @in_sendtime datetime
as
begin
  SET NOCOUNT ON;

  delete from RECVSMS where getdate() > dateadd(day, 1, SENDTIME);

  insert into RECVSMS(
    MSGID,
    USERID,
    MOBILE,
    CONTENT,
    HAVEREAD,
    SENDTIME
    )
    values(
    @in_msgid,
    @in_userid,
    @in_mobile,
    @in_content,
    @in_haveread,
    @in_sendtime
    );

  SET NOCOUNT OFF;
end;
GO

/* GETRECVSMSLIST */

CREATE PROCEDURE GETRECVSMSLIST 
    @in_userid int
/*
returns (
    out_msgid varchar(50),
    out_userid int,
    out_mobile varchar(20),
    out_content varchar(4000),
    out_haveread int,
    out_sendtime datetime)
*/
as
begin
  if (@in_userid > 0)
  begin
      select MSGID as out_msgid, USERID as out_userid, MOBILE as out_mobile, CONTENT as out_content, HAVEREAD as out_haveread, SENDTIME as out_sendtime
        from RECVSMS where USERID = @in_userid and getdate() < dateadd(day, 1, SENDTIME);

      SET NOCOUNT ON;
      update RECVSMS set HAVEREAD = 1 where USERID = @in_userid and getdate() < dateadd(day, 1, SENDTIME);
      SET NOCOUNT OFF;
  end
  else
  begin
      select MSGID as out_msgid, USERID as out_userid, MOBILE as out_mobile, CONTENT as out_content, HAVEREAD as out_haveread, SENDTIME as out_sendtime
        from RECVSMS where getdate() < dateadd(day, 1, SENDTIME);

      SET NOCOUNT ON;
      update RECVSMS set HAVEREAD = 1 where getdate() < dateadd(day, 1, SENDTIME);
      SET NOCOUNT OFF;
  end
end;
GO

/* ADDADMIN */

CREATE PROCEDURE ADDADMIN 
    @in_adminname nvarchar(20),
    @in_adminrights nvarchar(255),
    @in_adminpwd nvarchar(255),
    @in_description nvarchar(255),
    @in_islock smallint
/*
returns (
    @out_errorcode int,
    @out_adminname nvarchar(20),
    @out_adminrights nvarchar(255),
    @out_adminpwd nvarchar(255),
    @out_description nvarchar(255),
    @out_logined smallint,
    @out_islock smallint,
    @out_lastlogintime datetime,
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  if (exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname))) 
  begin
    set @out_errorcode = 1;
    goto result_label;
  end

  insert into ADMINS(
	ADMINNAME,
	ADMINRIGHTS,
	ADMINPWD,
	DESCRIPTION,
	LOGINED,
	ISLOCK,
	LASTLOGINTIME,
	CREATETIME
    )
    values(
	@in_adminname,
	@in_adminrights,
	@in_adminpwd,
	@in_description,
	0,
	0,
	null,
	getdate()
    );

    goto result_label2;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
result_label2:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode, ADMINNAME as out_adminname, ADMINRIGHTS as out_adminrights, ADMINPWD as out_adminpwd, 
       DESCRIPTION as out_description, LOGINED as out_logined, ISLOCK as out_islock, LASTLOGINTIME as out_lastlogintime, CREATETIME as out_createtime 
       from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname);
  return;
end;
GO

/* MODIFYADMIN */

CREATE PROCEDURE MODIFYADMIN 
    @in_adminname nvarchar(20),
    @in_adminrights nvarchar(255),
    @in_description nvarchar(255),
    @in_islock smallint
as
begin
  SET NOCOUNT ON;

  update ADMINS set ADMINRIGHTS = @in_adminrights, DESCRIPTION = @in_description, ISLOCK = @in_islock
    where LOWER(ADMINNAME) = LOWER(@in_adminname);

  SET NOCOUNT OFF;
end;
GO

/* VALIDATEADMIN */

CREATE PROCEDURE VALIDATEADMIN 
    @in_adminname nvarchar(20),
    @in_adminpwd nvarchar(255)
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_islock smallint;

  set @out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_adminpwd)) 
  begin
    set @out_errorcode = 1; /*---(not find admin) or (adminpwd error)---*/
    goto result_label;
  end

  set @var_islock = 0;
  select @var_islock = ISLOCK from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_adminpwd;
  if (@var_islock = 1) 
  begin
    set @out_errorcode = 2; /*---islock---*/
    goto result_label;
  end

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* ADMINLOGIN */

CREATE PROCEDURE ADMINLOGIN 
    @in_adminname nvarchar(20),
    @in_adminpwd nvarchar(255)
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  declare @var_islock smallint;

  set @out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_adminpwd)) 
  begin
    set @out_errorcode = 1; /*---(not find admin) or (adminpwd error)---*/
    goto result_label;
  end

  set @var_islock = 0;
  select @var_islock = ISLOCK from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_adminpwd;
  if (@var_islock = 1) 
  begin
    set @out_errorcode = 2; /*---islock---*/
    goto result_label;
  end

  update ADMINS set LOGINED = 1, LASTLOGINTIME = getdate()
    where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_adminpwd;

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* ADMINLOGOUT */

CREATE PROCEDURE ADMINLOGOUT 
    @in_adminname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  update ADMINS set LOGINED = 0 where LOWER(ADMINNAME) = LOWER(@in_adminname);

  SET NOCOUNT OFF;
end;
GO

/* LOCKADMIN */

CREATE PROCEDURE LOCKADMIN 
    @in_adminname nvarchar(20),
    @in_islock smallint
as
begin
  SET NOCOUNT ON;

  update ADMINS set ISLOCK = @in_islock where LOWER(ADMINNAME) = LOWER(@in_adminname);

  SET NOCOUNT OFF;
end;
GO

/* CHANGEADMINPWD */

CREATE PROCEDURE CHANGEADMINPWD 
    @in_adminname nvarchar(20),
    @in_oldpwd nvarchar(255),
    @in_newpwd nvarchar(255)
/*
returns (
    @out_errorcode int)
*/
as
begin
  SET NOCOUNT ON;

  declare @out_errorcode int;

  set @out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname) and ADMINPWD = @in_oldpwd)) 
  begin
    set @out_errorcode = 1; /*---(not find admin) or (oldpwd error)---*/
    goto result_label;
  end

  update ADMINS set 
    ADMINPWD = @in_newpwd
    where LOWER(ADMINNAME) = LOWER(@in_adminname);

result_label:
  SET NOCOUNT OFF;
  select @out_errorcode as out_errorcode;
  return;
end;
GO

/* REMOVEADMIN */

CREATE PROCEDURE REMOVEADMIN 
    @in_adminname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  delete from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname);

  SET NOCOUNT OFF;
end;
GO

/* GETADMIN */

CREATE PROCEDURE GETADMIN 
    @in_adminname varchar(20)
/*
returns (
    out_adminname nvarchar(20),
    out_adminrights nvarchar(255),
    out_description nvarchar(255),
    out_logined smallint,
    out_islock smallint,
    out_lastlogintime datetime,
    out_createtime datetime)
*/
as
begin
  select ADMINNAME as out_adminname, ADMINRIGHTS as out_adminrights, DESCRIPTION as out_description, 
       LOGINED as out_logined, ISLOCK as out_islock, LASTLOGINTIME as out_lastlogintime, CREATETIME as out_createtime
       from ADMINS where LOWER(ADMINNAME) = LOWER(@in_adminname);
end;
GO

/* GETADMINS */

CREATE PROCEDURE GETADMINS
/*
returns (
    @out_adminname nvarchar(20),
    @out_adminrights nvarchar(255),
    @out_description nvarchar(255),
    @out_logined smallint,
    @out_islock smallint,
    @out_lastlogintime datetime,
    @out_createtime datetime)
*/
as
begin
  select ADMINNAME as out_adminname, ADMINRIGHTS as out_adminrights, DESCRIPTION as out_description, LOGINED as out_logined, 
       ISLOCK as out_islock, LASTLOGINTIME as out_lastlogintime, CREATETIME as out_createtime
       from ADMINS where 1 = 1;
end;
GO

/* RESETLOGINEDADMINS */

CREATE PROCEDURE RESETLOGINEDADMINS
as
begin
  SET NOCOUNT ON;

  update ADMINS set LOGINED = 0 where LOGINED = 1;

  SET NOCOUNT OFF;
end;
GO

/* ADDADMINLOG */

CREATE PROCEDURE ADDADMINLOG 
    @in_adminname nvarchar(20),
    @in_description nvarchar(255),
    @in_ipaddr nvarchar(20)
/*
returns (
    @out_sid bigint,
    @out_adminname nvarchar(20),
    @out_description nvarchar(255),
    @out_ipaddr nvarchar(20),
    @out_createtime datetime)
*/
as
begin
  SET NOCOUNT ON;

  delete from ADMINLOGS where getdate() > dateadd(day, 90, CREATETIME);

  insert into ADMINLOGS(
	ADMINNAME,
	DESCRIPTION,
	IPADDR,
	CREATETIME
    )
    values(
	@in_adminname,
	@in_description,
	@in_ipaddr,
	getdate()
    );

  SET NOCOUNT OFF;

  select SID as out_sid, ADMINNAME as out_adminname, DESCRIPTION as out_description, 
       IPADDR as out_ipaddr, CREATETIME as out_createtime 
       from ADMINLOGS where SID = SCOPE_IDENTITY();
end;
GO

/* GETADMINLOGS */

CREATE PROCEDURE GETADMINLOGS 
    @in_pagesize int,
    @in_pageindex int
/*
returns (
    @out_maxpageindex int,
    @out_sid bigint,
    @out_adminname nvarchar(20),
    @out_description nvarchar(255),
    @out_ipaddr nvarchar(20),
    @out_createtime datetime)
*/
as
begin
  declare @out_maxpageindex int;

  declare @var_adminlogcount int;
  declare @var_where_sql nvarchar(1024);
  declare @var_fields_sql nvarchar(4000);
  declare @var_padding_sql nvarchar(4000);

  if (@in_pagesize <= 0)  set @in_pagesize = 1;

  select @var_adminlogcount = count(*) from ADMINLOGS where 1 = 1;
  set @out_maxpageindex = @var_adminlogcount / @in_pagesize;
  if ((@var_adminlogcount % @in_pagesize) > 0) 
  begin
    set @out_maxpageindex = @out_maxpageindex + 1;
  end

  if (@out_maxpageindex <= 0) set @out_maxpageindex = 1;

  if ((@in_pageindex > @out_maxpageindex) or (@in_pageindex <= 0)) 
  begin
    set @in_pageindex = @out_maxpageindex;
  end

  set @var_where_sql = '(1 = 1) order by CREATETIME DESC';

  set @var_fields_sql = 
      str(@out_maxpageindex) + ' as out_maxpageindex, ' +
      ' SID as out_sid, ADMINNAME as out_adminname, DESCRIPTION as out_description,' +
      ' IPADDR as out_ipaddr, CREATETIME as out_createtime';

  if (@in_pageindex = 1) 
  begin
    set @var_padding_sql =
      'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
        ' from ADMINLOGS where ' + @var_where_sql;
  end
  else
  begin
    set @var_padding_sql = 'select top ' + str(@in_pagesize) + ' ' + @var_fields_sql +
       ' from ADMINLOGS where ' +
          ' (SID < (select min(SID) from ' +
	  '    (select top ' + str(@in_pagesize * (@in_pageindex - 1)) + ' SID from ADMINLOGS where ' + @var_where_sql + ') as T' + 
	  ')) and ' + @var_where_sql;
  end;

  exec(@var_padding_sql);
end;
GO

/* ADDJOINREQUEST */

CREATE PROCEDURE ADDJOINREQUEST 
    @in_userid int,
    @in_content nvarchar(255),
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINREQUESTS where USERID = @in_userid and SENDER = @in_sender)) 
  begin
    update JOINREQUESTS set CONTENT = @in_content 
      where USERID = @in_userid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINREQUESTS(USERID, CONTENT, SENDER, SENDTIME) 
      values(@in_userid, @in_content, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINREQUESTS */

CREATE PROCEDURE GETJOINREQUESTS 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_content nvarchar(255),
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, CONTENT as out_content, SENDER as out_sender, SENDTIME as out_sendtime
	from JOINREQUESTS where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINREQUESTS where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDJOINRESPONSE */

CREATE PROCEDURE ADDJOINRESPONSE 
    @in_userid int,
    @in_content nvarchar(255),
    @in_agree smallint,
    @in_sender int
as
begin
  SET NOCOUNT ON;

  if (exists(select * from JOINRESPONSES where USERID = @in_userid and SENDER = @in_sender)) 
  begin
    update JOINRESPONSES set CONTENT = @in_content, AGREE = @in_agree 
      where USERID = @in_userid and SENDER = @in_sender;
  end
  else
  begin
    insert into JOINRESPONSES(USERID, CONTENT, AGREE, SENDER, SENDTIME) 
      values(@in_userid, @in_content, @in_agree, @in_sender, getdate());
  end

  SET NOCOUNT OFF;
end;
GO

/* GETJOINRESPONSES */

CREATE PROCEDURE GETJOINRESPONSES 
    @in_userid int
/*
returns(
    @out_sid bigint,
    @out_userid int,
    @out_content nvarchar(255),
    @out_agree smallint,
    @out_sender int,
    @out_sendtime datetime)
*/
as
begin
  select SID as out_sid, USERID as out_userid, CONTENT as out_content, AGREE as out_agree, SENDER as out_sender, SENDTIME as out_sendtime 
	from JOINRESPONSES where USERID = @in_userid;

  SET NOCOUNT ON;

  delete from JOINRESPONSES where USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* ADDOFFLINEMESSAGE */

CREATE PROCEDURE ADDOFFLINEMESSAGE 
    @in_msgid varchar(50),
    @in_userid int,
    @in_msgtype smallint,
    @in_msglevel smallint,
    @in_content nvarchar(4000),
    @in_font nvarchar(50),
    @in_sender_userid int,
    @in_sender_nickname nvarchar(20),
    @in_sender_actualname nvarchar(20)
as
begin
  SET NOCOUNT ON;

  insert into OFFLINEMESSAGES(
    MSGID,
    USERID,
    MSGTYPE,
    MSGLEVEL,
    CONTENT,
    FONT,
    SENDER_USERID,
    SENDER_NICKNAME,
    SENDER_ACTUALNAME,
    SENDTIME
  ) values (
    @in_msgid,
    @in_userid,
    @in_msgtype,
    @in_msglevel,
    @in_content,
    @in_font,
    @in_sender_userid,
    @in_sender_nickname,
    @in_sender_actualname,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFLINEMESSAGE */

CREATE PROCEDURE REMOVEOFFLINEMESSAGE 
    @in_msgid varchar(50),
    @in_userid int
as
begin
  SET NOCOUNT ON;

  delete from OFFLINEMESSAGES where MSGID = @in_msgid and USERID = @in_userid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFLINEMESSAGES */

CREATE PROCEDURE GETOFFLINEMESSAGES 
    @in_userid int
/*
returns (
    @out_msgid varchar(50),
    @out_userid int,
    @out_msgtype smallint,
    @out_msglevel smallint,
    @out_content nvarchar(8192),
    @out_font nvarchar(50),
    @out_sender_userid int,
    @out_sender_nickname nvarchar(20),
    @out_sender_actualname nvarchar(20),
    @out_sendtime datetime)
*/
as
begin
  select MSGID as out_msgid, USERID as out_userid, MSGTYPE as out_msgtype, MSGLEVEL as out_msglevel, 
      CONTENT as out_content, FONT as out_font, SENDER_USERID as out_sender_userid, SENDER_NICKNAME as out_sender_nickname, 
      SENDER_ACTUALNAME as out_sender_actualname, SENDTIME as out_sendtime 
      from OFFLINEMESSAGES where USERID = @in_userid order by SENDTIME ASC;
end;
GO

/* ADDOFFLINESUPEROBJECT */

CREATE PROCEDURE ADDOFFLINESUPEROBJECT 
    @in_msgid varchar(50),
    @in_fromuserid int,
    @in_superobjectcode nvarchar(1024),
    @in_localfilename nvarchar(1024)
as
begin
  SET NOCOUNT ON;

  insert into OFFLINESUPEROBJECTS(
    MSGID,
    FROMUSERID,
    SUPEROBJECTCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    @in_msgid,
    @in_fromuserid,
    @in_superobjectcode,
    @in_localfilename,
    getdate()
  );

  SET NOCOUNT OFF;
end;
GO

/* REMOVEOFFLINESUPEROBJECT */

CREATE PROCEDURE REMOVEOFFLINESUPEROBJECT 
    @in_sid bigint
as
begin
  SET NOCOUNT ON;

  delete from OFFLINESUPEROBJECTS where SID = @in_sid;

  SET NOCOUNT OFF;
end;
GO

/* GETOFFLINESUPEROBJECTS */

CREATE PROCEDURE GETOFFLINESUPEROBJECTS 
    @in_msgid varchar(50),
    @in_fromuserid int
/*
returns (
    @out_sid bigint,
    @out_msgid varchar(50),
    @out_fromuserid int,
    @out_superobjectcode nvarchar(1024),
    @out_localfilename nvarchar(1024),
    @out_createtime datetime)
*/
as
begin
  select SID as out_sid, MSGID as out_msgid, FROMUSERID as out_fromuserid, SUPEROBJECTCODE as out_superobjectcode, 
    LOCALFILENAME as out_localfilename, CREATETIME as out_createtime
    from OFFLINESUPEROBJECTS where MSGID = @in_msgid and FROMUSERID = @in_fromuserid;
end;
GO

/* GETUSERPUSH */

CREATE PROCEDURE GETUSERPUSH 
    @in_userid int
/*
returns (
    out_clientid varchar(50),
    out_os smallint,
    out_badge int,
    out_needpush smallint,
    out_isactive smallint)
*/
as
begin
  select a.CLIENTID as out_clientid, a.OS as out_os, a.BADGE as out_badge, 
       a.NEEDPUSH as out_needpush, a.ISACTIVE as out_isactive
       from PUSH as a
       where a.USERID=@in_userid and a.ISACTIVE=1 and a.NEEDPUSH=1;
end;
GO

/* UPDATEUSERPUSH */

CREATE PROCEDURE UPDATEUSERPUSH 
    @in_userid int,
    @in_clientid varchar(50),
    @in_device varchar(50),
    @in_os smallint,
    @in_badge int,
    @in_iosdevicetoken varchar(100),
    @in_needpush smallint
as
begin
  declare @var_newpushid int;

  if (@in_clientid <>'') 
  begin
	  if (exists (select * from PUSH where USERID = @in_userid and CLIENTID= @in_clientid)) 
	  begin
		  update PUSH set
			BADGE = @in_badge,
			ISACTIVE = 1,
			NEEDPUSH = @in_needpush
			where USERID = @in_userid and CLIENTID= @in_clientid;
	  end
	  else
	  begin
		  select @var_newpushid = max(PUSHID) from PUSH;
		  if (@var_newpushid is null) set @var_newpushid = 1;
		  set @var_newpushid = @var_newpushid + 1;


		  insert into PUSH(
			PUSHID,
			USERID,
			CLIENTID,
			DEVICE,
			OS,
			BADGE,
			IOSDEVICETOKEN,
			ISACTIVE,
			NEEDPUSH,
			CREATETIME
			)
			values(
			@var_newpushid,
			@in_userid,
			@in_clientid,
			@in_device,
			@in_os,
			@in_badge,
			@in_iosdevicetoken,
			1,
			@in_needpush,
			getdate()
			);

	  end
  end
  
  update PUSH set
	ISACTIVE = 0
	where USERID = @in_userid and CLIENTID<> @in_clientid;
end;
GO

/* INCPUSHBADGE */

CREATE PROCEDURE INCPUSHBADGE 
    @in_userid int,
    @in_clientid varchar(50),
    @in_badge int
as
begin
  if (exists (select * from PUSH where USERID = @in_userid and CLIENTID= @in_clientid)) 
  begin
      update PUSH set
        BADGE = @in_badge
        where USERID = @in_userid and CLIENTID= @in_clientid;

  end
end;
GO