set term ## ;

set sql dialect 3##

-- The Nvl family is deprecated. Use native COALESCE instead.
--FBUDF_API paramdsc* idNvl(paramdsc* v, paramdsc* v2)
declare external function invl
int by descriptor, int by descriptor
returns int by descriptor
entry_point 'idNvl' module_name 'fbudf'##

--FBUDF_API paramdsc* idNvl(paramdsc* v, paramdsc* v2)
declare external function i64nvl
numeric(18,0) by descriptor, numeric(18,0) by descriptor
returns numeric(18,0) by descriptor
entry_point 'idNvl' module_name 'fbudf'##

--FBUDF_API paramdsc* idNvl(paramdsc* v, paramdsc* v2)
declare external function dnvl
double precision by descriptor, double precision by descriptor
returns double precision by descriptor
entry_point 'idNvl' module_name 'fbudf'##

--FBUDF_API paramdsc* sNvl(paramdsc* v, paramdsc* v2, paramdsc* rc)
declare external function snvl
varchar(100) by descriptor, varchar(100) by descriptor,
varchar(100) by descriptor returns parameter 3
entry_point 'sNvl' module_name 'fbudf'##

-- The NullIf family is deprecated. Use native NULLIF instead.
--FBUDF_API paramdsc* iNullIf(paramdsc* v, paramdsc* v2)
declare external function inullif
int by descriptor, int by descriptor
returns int by descriptor
entry_point 'iNullIf' module_name 'fbudf'##

--FBUDF_API paramdsc* dNullIf(paramdsc* v, paramdsc* v2)
declare external function dnullif
double precision by descriptor, double precision by descriptor
returns double precision by descriptor
entry_point 'dNullIf' module_name 'fbudf'##

--FBUDF_API paramdsc* iNullIf(paramdsc* v, paramdsc* v2)
declare external function i64nullif
numeric(18,4) by descriptor, numeric(18,4) by descriptor
returns numeric(18,4) by descriptor
entry_point 'iNullIf' module_name 'fbudf'##

--FBUDF_API paramdsc* sNullIf(paramdsc* v, paramdsc* v2, paramdsc* rc)
declare external function snullif
varchar(100) by descriptor, varchar(100) by descriptor,
varchar(100) by descriptor returns parameter 3
entry_point 'sNullIf' module_name 'fbudf'##

--FBUDF_API char* DOW(ISC_DATE* v, char* rc)
declare external function dow
timestamp,
varchar(15) returns parameter 2
entry_point 'DOW' module_name 'fbudf'##

--FBUDF_API char* SDOW(ISC_DATE* v, char* rc)
declare external function sdow
timestamp,
varchar(5) returns parameter 2
entry_point 'SDOW' module_name 'fbudf'##

--FBUDF_API paramdsc* right(const paramdsc*, const ISC_SHORT& rl, paramdsc* rc)
declare external function sright
varchar(100) by descriptor, smallint,
varchar(100) by descriptor returns parameter 3
entry_point 'right' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addDay(ISC_TIMESTAMP* v, const ISC_SLONG& ndays)
declare external function addDay
timestamp, int
returns timestamp
entry_point 'addDay' module_name 'fbudf'##

--FBUDF_API void addDay2(const ISC_TIMESTAMP* v0, const ISC_SLONG& ndays, ISC_TIMESTAMP* v)
declare external function addDay2
timestamp, int, timestamp
returns parameter 3
entry_point 'addDay2' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addWeek(ISC_TIMESTAMP* v, const ISC_SLONG& nweeks)
declare external function addWeek
timestamp, int
returns timestamp
entry_point 'addWeek' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addMonth(ISC_TIMESTAMP* v, const ISC_SLONG& nmonths)
declare external function addMonth
timestamp, int
returns timestamp
entry_point 'addMonth' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addYear(ISC_TIMESTAMP* v, const ISC_SLONG& nyears)
declare external function addYear
timestamp, int
returns timestamp
entry_point 'addYear' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addMilliSecond(ISC_TIMESTAMP* v, const ISC_SLONG& nseconds)
declare external function addMilliSecond
timestamp, int
returns timestamp
entry_point 'addMilliSecond' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addSecond(ISC_TIMESTAMP* v, const ISC_SLONG& nseconds)
declare external function addSecond
timestamp, int
returns timestamp
entry_point 'addSecond' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addMinute(ISC_TIMESTAMP* v, const ISC_SLONG& nminutes)
declare external function addMinute
timestamp, int
returns timestamp
entry_point 'addMinute' module_name 'fbudf'##

--FBUDF_API ISC_TIMESTAMP* addHour(ISC_TIMESTAMP* v, const ISC_SLONG& nhours)
declare external function addHour
timestamp, int
returns timestamp
entry_point 'addHour' module_name 'fbudf'##

--It will work only with Win32 until it's ported to another OS.
--FBUDF_API ISC_TIMESTAMP* getExactTimestamp(ISC_TIMESTAMP* rc)
declare external function getExactTimestamp
timestamp
returns parameter 1
entry_point 'getExactTimestamp' module_name 'fbudf'##

--It will work only with Win32 until it's ported to another OS.
--FBUDF_API void getExactTimestampUTC(ISC_TIMESTAMP* rc)
declare external function getExactTimestampUTC
timestamp
returns parameter 1
entry_point 'getExactTimestampUTC' module_name 'fbudf'##

--FBUDF_API int isLeapYear(const ISC_TIMESTAMP* v);
declare external function isLeapYear
timestamp
returns int by value
entry_point 'isLeapYear' module_name 'fbudf'##

--FBUDF_API paramdsc* fbtruncate(const paramdsc* v, paramdsc* rc)
declare external function Truncate
int by descriptor, int by descriptor
returns parameter 2
entry_point 'fbtruncate' module_name 'fbudf'##

--FBUDF_API paramdsc* fbtruncate(const paramdsc* v, paramdsc* rc)
declare external function i64Truncate
numeric(18) by descriptor, numeric(18) by descriptor
returns parameter 2
entry_point 'fbtruncate' module_name 'fbudf'##

--FBUDF_API paramdsc* fbround(const paramdsc* v, paramdsc* rc)
declare external function Round
int by descriptor, int by descriptor
returns parameter 2
entry_point 'fbround' module_name 'fbudf'##

--FBUDF_API paramdsc* fbround(const paramdsc* v, paramdsc* rc)
declare external function i64Round
numeric(18, 4) by descriptor, numeric(18, 4) by descriptor
returns parameter 2
entry_point 'fbround' module_name 'fbudf'##

--FBUDF_API paramdsc* power(const paramdsc* v, const paramdsc* v2, paramdsc* rc)
declare external function dPower
double precision by descriptor, double precision by descriptor,
double precision by descriptor
returns parameter 3
entry_point 'power' module_name 'fbudf'##

--FBUDF_API blobcallback* string2blob(const paramdsc* v, blobcallback* outblob)
declare external function string2blob
varchar(300) by descriptor,
blob returns parameter 2
entry_point 'string2blob' module_name 'fbudf'##


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

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

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

/*[ADDRLSTCATEGORYS]*/
/*[ADDRLSTITEMS]*/

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

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

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

/*[WEBAPPS]*/

/*[RECENTITEMS]*/

/*[SYSMSGS]*/
/*[SYSMSGDETAILS]*/

/*[SENDSMS]*/
/*[RECVSMS]*/

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

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

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

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

/*[ACCOUNTSECTIONS]*/

CREATE TABLE ACCOUNTSECTIONS (
    BEGINUSERID        INTEGER NOT NULL,
    ENDUSERID          INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ACCOUNTSECTIONS ADD CONSTRAINT PK_ACCOUNTSECTIONS PRIMARY KEY (BEGINUSERID, ENDUSERID)##

CREATE INDEX ACCOUNTSECTIONS_IDX_CREATETIME ON ACCOUNTSECTIONS (CREATETIME)##

/*[ACCOUNTS]*/

CREATE TABLE ACCOUNTS (
    USERID             INTEGER NOT NULL,
    RANDOMVALUE        DOUBLE PRECISION NOT NULL,
    REGISTED           SMALLINT NOT NULL,
    REGISTERTIME       TIMESTAMP,
    REGISTERIPADDR     VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    SECTION            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY (USERID)##

CREATE INDEX ACCOUNTS_IDX_RANDOMVALUE ON ACCOUNTS (RANDOMVALUE)##

CREATE INDEX ACCOUNTS_IDX_REGISTED ON ACCOUNTS (REGISTED)##

CREATE INDEX ACCOUNTS_IDX_CREATETIME ON ACCOUNTS (CREATETIME)##

/*[USERS]*/

CREATE TABLE USERS (
    USERID             INTEGER NOT NULL,
    USERNAME           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    USERTYPE           SMALLINT NOT NULL,
    CANFOUND           SMALLINT NOT NULL,

    NICKNAME           VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SIGNATURE          VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    ACTUALNAME         VARCHAR(20) CHARACTER SET UNICODE_FSS 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            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE_SHORT    VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE2         VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE2_SHORT   VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE             VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE_SHORT       VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE2            VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE2_SHORT      VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    FAX                VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    QQ                 VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    WEIXIN             VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    WEIBO              VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    EMAIL              VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ROOMNAME           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ROOMNUMBER         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    IDNUMBER           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CARNUMBER          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    HOMEPAGE           VARCHAR(80) CHARACTER SET UNICODE_FSS NOT NULL,
    DEPARTID           INTEGER NOT NULL,
    DEPARTNAME         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    JOBTITLE           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    JOBNUMBER          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    INTRODUCTION       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,

    FACEFILE           VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    PHOTOFILE          VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,

    LOGINSTATUS        SMALLINT NOT NULL,
    LOGINTIMES         BIGINT NOT NULL,
    LASTLOGINTIME      TIMESTAMP,
    CLIENTIPADDR       VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CLIENTLOCATION     VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    LASTCLIENTIPADDR   VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    LASTCLIENTLOCATION VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,  

    PWD                VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    SALT               VARCHAR(6) CHARACTER SET UNICODE_FSS NOT NULL,
    TOKEN              VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    TOKENUPDATETIME    TIMESTAMP,
    LOCKED             SMALLINT NOT NULL,

    ONLYFINDMEBYID     SMALLINT NOT NULL,
    JOINSETTING        SMALLINT NOT NULL,
    JOINQUESTION       VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    JOINANSWER         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,

    LASTRECVSYSMSGS    TIMESTAMP,
    LINKDEPARTID       INTEGER NOT NULL,
    USERPOSITION       INTEGER NOT NULL,

    SMSTOTAL           INTEGER NOT NULL,
    SMSDAYLIMIT        INTEGER NOT NULL,
    SMSDAYSTART        TIMESTAMP,
    SMSDAYSENDED       INTEGER NOT NULL,
    SMSTOTALSENDED     INTEGER NOT NULL,    

    MODIFYTIME         TIMESTAMP NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE USERS ADD CONSTRAINT PK_USERS PRIMARY KEY (USERID)##

CREATE UNIQUE INDEX USERS_IDX_USERNAME ON USERS (USERNAME)##

CREATE INDEX USERS_IDX_USERTYPE ON USERS (USERTYPE)##

CREATE INDEX USERS_IDX_CANFOUND ON USERS (CANFOUND)##

CREATE INDEX USERS_IDX_NICKNAME ON USERS (NICKNAME)##

CREATE INDEX USERS_IDX_ACTUALNAME ON USERS (ACTUALNAME)##

CREATE INDEX USERS_IDX_SEX ON USERS (SEX)##

CREATE INDEX USERS_IDX_AGE ON USERS (AGE)##

CREATE INDEX USERS_IDX_COUNTRY ON USERS (COUNTRY)##

CREATE INDEX USERS_IDX_PROVINCE ON USERS (PROVINCE)##

CREATE INDEX USERS_IDX_CITY ON USERS (CITY)##

CREATE INDEX USERS_IDX_AREA ON USERS (AREA)##

CREATE INDEX USERS_IDX_DEPARTID ON USERS (DEPARTID)##

CREATE INDEX USERS_IDX_LOGINSTATUS ON USERS (LOGINSTATUS)##

CREATE INDEX USERS_IDX_PWD ON USERS (PWD)##

CREATE INDEX USERS_IDX_SALT ON USERS (SALT)##

CREATE INDEX USERS_IDX_TOKEN ON USERS (TOKEN)##

CREATE INDEX USERS_IDX_LOCKED ON USERS (LOCKED)##

CREATE INDEX USERS_IDX_ONLYFINDMEBYID ON USERS (ONLYFINDMEBYID)##

CREATE INDEX USERS_IDX_CREATETIME ON USERS (CREATETIME)##

/*[PWDPROTECTION]*/

CREATE TABLE PWDPROTECTION (
    USERID             INTEGER NOT NULL,
    QUESTION1          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ANSWER1            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    QUESTION2          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ANSWER2            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    QUESTION3          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ANSWER3            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    LASTMODIFYTIME     TIMESTAMP NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE PWDPROTECTION ADD CONSTRAINT PK_PWDPROTECTION PRIMARY KEY (USERID)##

CREATE INDEX PWDPROTECTION_IDX_CREATETIME ON PWDPROTECTION (CREATETIME)##

/*[DEPARTMENTS]*/

CREATE TABLE DEPARTMENTS (
    DEPARTID           INTEGER NOT NULL,
    DEPARTNAME         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ORDERVALUE         INTEGER NOT NULL,
    PARENTDEPARTID     INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE DEPARTMENTS ADD CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTID)##

CREATE INDEX DEPARTMENTS_IDX_ORDERVALUE ON DEPARTMENTS (ORDERVALUE)##

CREATE INDEX DEPARTMENTS_IDX_PARENTDEPARTID ON DEPARTMENTS (PARENTDEPARTID)##

CREATE INDEX DEPARTMENTS_IDX_CREATETIME ON DEPARTMENTS (CREATETIME)##

/*[CONTACTGROUPS]*/

CREATE TABLE CONTACTGROUPS (
    CONTACTGROUPID     INTEGER NOT NULL,
    USERID             INTEGER NOT NULL,
    CONTACTGROUPNAME   VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    HIDEWHENONLINE     SMALLINT NOT NULL,
    ONLINEWHENHIDE     SMALLINT NOT NULL,
    REMINDMEWHENLOGIN  SMALLINT NOT NULL,
    NOMESSAGE          SMALLINT NOT NULL,
    ORDERVALUE         INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE CONTACTGROUPS ADD CONSTRAINT PK_CONTACTGROUPS PRIMARY KEY (CONTACTGROUPID, USERID)##

CREATE INDEX CONTACTGROUPS_IDX_ORDERVALUE ON CONTACTGROUPS (ORDERVALUE)##

CREATE INDEX CONTACTGROUPS_IDX_CREATETIME ON CONTACTGROUPS (CREATETIME)##

/*[CONTACTS]*/

CREATE TABLE CONTACTS (
    CONTACTID          INTEGER NOT NULL,
    USERID             INTEGER NOT NULL,
    CONTACTGROUPID     INTEGER NOT NULL,
    REMARKNAME         VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    HIDEWHENONLINE     SMALLINT NOT NULL,
    ONLINEWHENHIDE     SMALLINT NOT NULL,
    REMINDMEWHENLOGIN  SMALLINT NOT NULL,
    NOMESSAGE          SMALLINT NOT NULL,
    JOINTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE CONTACTS ADD CONSTRAINT PK_CONTACTS PRIMARY KEY (CONTACTID,USERID)##

CREATE INDEX CONTACTS_IDX_CONTACTGROUPID ON CONTACTS (CONTACTGROUPID)##

CREATE INDEX CONTACTS_IDX_JOINTIME ON CONTACTS (JOINTIME)##

/*[ADDRLSTCATEGORYS]*/

CREATE TABLE ADDRLSTCATEGORYS (
    CATEGORYID         INTEGER NOT NULL,
    USERID             INTEGER NOT NULL,    
    CATEGORYNAME       VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ORDERVALUE         INTEGER NOT NULL,
    PARENTCATEGORYID   INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ADDRLSTCATEGORYS ADD CONSTRAINT PK_ADDRLSTCATEGORYS PRIMARY KEY (CATEGORYID)##

CREATE INDEX ADDRLSTCATEGORYS_IDX_USERID ON ADDRLSTCATEGORYS (USERID)##

CREATE INDEX ADDRLSTCATEGORYS_IDX_ORDERVALUE ON ADDRLSTCATEGORYS (ORDERVALUE)##

CREATE INDEX ADDRLSTCATEGORYS_IDX_P_C_ID ON ADDRLSTCATEGORYS (PARENTCATEGORYID)##

CREATE INDEX ADDRLSTCATEGORYS_IDX_CREATETIME ON ADDRLSTCATEGORYS (CREATETIME)##

/*[ADDRLSTITEMS]*/

CREATE TABLE ADDRLSTITEMS (
    ITEMID             INTEGER NOT NULL,
    CATEGORYID         INTEGER NOT NULL,
    USERID             INTEGER NOT NULL,    
    ACTUALNAME         VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SEX                SMALLINT NOT NULL,
    AGE                SMALLINT NOT NULL,
    ZIPCODE            VARCHAR(10) CHARACTER SET UNICODE_FSS NOT NULL,
    ADDRESS            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE_SHORT    VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE2         VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    TELEPHONE2_SHORT   VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE             VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE_SHORT       VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE2            VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    MOBILE2_SHORT      VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    FAX                VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    QQ                 VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    WEIXIN             VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    WEIBO              VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    EMAIL              VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ROOMNAME           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    ROOMNUMBER         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    IDNUMBER           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CARNUMBER          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    DESCRIPTION        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ADDRLSTITEMS ADD CONSTRAINT PK_ADDRLSTITEMS PRIMARY KEY (ITEMID)##

CREATE INDEX ADDRLSTITEMS_IDX_CATEGORYID ON ADDRLSTITEMS (CATEGORYID)##

CREATE INDEX ADDRLSTITEMS_IDX_USERID ON ADDRLSTITEMS (USERID)##

CREATE INDEX ADDRLSTITEMS_IDX_CREATETIME ON ADDRLSTITEMS (CREATETIME)##

/*[GROUPS]*/

CREATE TABLE GROUPS (
    GROUPID            INTEGER NOT NULL,
    GROUPNAME          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    INTRODUCTION       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    NOTICE             VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    JOINSETTING        SMALLINT NOT NULL,
    CREATORID          INTEGER NOT NULL,
    OWNERID            INTEGER NOT NULL,
    OWNERUSERTYPE      SMALLINT NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY (GROUPID)##

CREATE INDEX GROUPS_IDX_GROUPNAME ON GROUPS (GROUPNAME)##

CREATE INDEX GROUPS_IDX_CREATORID ON GROUPS (CREATORID)##

CREATE INDEX GROUPS_IDX_OWNERID ON GROUPS (OWNERID)##

CREATE INDEX GROUPS_IDX_OWNERUSERTYPE ON GROUPS (OWNERUSERTYPE)##

CREATE INDEX GROUPS_IDX_CREATETIME ON GROUPS (CREATETIME)##

/*[GROUPMEMBERS]*/

CREATE TABLE GROUPMEMBERS (
    MEMBERID           INTEGER NOT NULL,
    GROUPID            INTEGER NOT NULL,
    ISMANAGER          SMALLINT NOT NULL,
    LASTRECVMSGSID     BIGINT NOT NULL,
    MSGHINTSETTING     SMALLINT NOT NULL,
    MODIFYCARDBYMNG    SMALLINT NOT NULL,
    CARD_NAME          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_SEX           SMALLINT NOT NULL,
    CARD_NUMBER        VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_EMAIL         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_REMARK        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,    
    JOINTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE GROUPMEMBERS ADD CONSTRAINT PK_GROUPMEMBERS PRIMARY KEY (MEMBERID,GROUPID)##

CREATE INDEX GROUPMEMBERS_IDX_ISMANAGER ON GROUPMEMBERS (ISMANAGER)##

CREATE INDEX GROUPMEMBERS_IDX_LASTRECVMSGSID ON GROUPMEMBERS (LASTRECVMSGSID)##

CREATE INDEX GROUPMEMBERS_IDX_JOINTIME ON GROUPMEMBERS (JOINTIME)##

/*[GROUPSHAREFILES]*/

CREATE TABLE GROUPSHAREFILES (
    SID                VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    MEMBERID           INTEGER NOT NULL,
    GROUPID            INTEGER NOT NULL,
    FILENAMEORDIR      VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    ISDIR              SMALLINT NOT NULL,
    ALLFILECOUNT       INTEGER NOT NULL,
    ALLFILESIZE        BIGINT NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE GROUPSHAREFILES ADD CONSTRAINT PK_GROUPSHAREFILES PRIMARY KEY (SID)##

CREATE INDEX GROUPSHAREFILES_IDX_MEMBERID ON GROUPSHAREFILES (MEMBERID)##

CREATE INDEX GROUPSHAREFILES_IDX_GROUPID ON GROUPSHAREFILES (GROUPID)##

CREATE INDEX GROUPSHAREFILES_IDX_CREATETIME ON GROUPSHAREFILES (CREATETIME)##

/*[OFFGROUPMSGS]*/

CREATE TABLE OFFGROUPMSGS (
    SID                BIGINT NOT NULL,
    GROUPID            INTEGER NOT NULL,
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    MSGLEVEL           SMALLINT NOT NULL,
    CONTENT            VARCHAR(8192) CHARACTER SET UNICODE_FSS NOT NULL,
    FONT               VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_USERID      INTEGER NOT NULL,
    SENDER_NICKNAME    VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_ACTUALNAME  VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE OFFGROUPMSGS ADD CONSTRAINT PK_OFFGROUPMSGS PRIMARY KEY (SID)##

CREATE INDEX OFFGROUPMSGS_IDX_GROUPID ON OFFGROUPMSGS (GROUPID)##

CREATE INDEX OFFGROUPMSGS_IDX_MSGID ON OFFGROUPMSGS (MSGID)##

CREATE INDEX OFFGROUPMSGS_IDX_SENDER_USERID ON OFFGROUPMSGS (SENDER_USERID)##

CREATE INDEX OFFGROUPMSGS_IDX_S_NICKNAME ON OFFGROUPMSGS (SENDER_NICKNAME)##

CREATE INDEX OFFGROUPMSGS_IDX_SENDTIME ON OFFGROUPMSGS (SENDTIME)##

CREATE GENERATOR OFFGROUPMSGS_GEN##

CREATE TRIGGER OFFGROUPMSGS_GEN_TRIG FOR OFFGROUPMSGS
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(OFFGROUPMSGS_GEN, 1);
END##

/*[OFFGROUPSUPEROBJS]*/

CREATE TABLE OFFGROUPSUPEROBJS (
    SID                BIGINT NOT NULL,    
    GROUPID            INTEGER NOT NULL,
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    FROMUSERID         INTEGER NOT NULL,
    SUPEROBJCODE       VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    LOCALFILENAME      VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE OFFGROUPSUPEROBJS ADD CONSTRAINT PK_OFFGROUPSUPEROBJS PRIMARY KEY (SID)##

CREATE INDEX OFFGROUPSUPEROBJS_IDX_GROUPID ON OFFGROUPSUPEROBJS (GROUPID)##

CREATE INDEX OFFGROUPSUPEROBJS_IDX_MSGID ON OFFGROUPSUPEROBJS (MSGID)##

CREATE INDEX OFFGROUPSUPEROBJS_IDX_CTIME ON OFFGROUPSUPEROBJS (CREATETIME)##

CREATE GENERATOR OFFGROUPSUPEROBJS_GEN##

CREATE TRIGGER OFFGROUPSUPEROBJS_GEN_TRIG FOR OFFGROUPSUPEROBJS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(OFFGROUPSUPEROBJS_GEN, 1); 
END##

/*[JOINGROUPREQUESTS]*/

CREATE TABLE JOINGROUPREQUESTS (
    SID                BIGINT NOT NULL,
    USERID             INTEGER NOT NULL,
    GROUPID            INTEGER NOT NULL,
    CONTENT            VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER             INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE JOINGROUPREQUESTS ADD CONSTRAINT PK_JOINGROUPREQUESTS PRIMARY KEY (SID)##

CREATE INDEX JOINGROUPREQUESTS_IDX_USERID ON JOINGROUPREQUESTS (USERID)##

CREATE INDEX JOINGROUPREQUESTS_IDX_GROUPID ON JOINGROUPREQUESTS (GROUPID)##

CREATE INDEX JOINGROUPREQUESTS_IDX_SENDER ON JOINGROUPREQUESTS (SENDER)##

CREATE INDEX JOINGROUPREQUESTS_IDX_SENDTIME ON JOINGROUPREQUESTS (SENDTIME)##

CREATE GENERATOR JOINGROUPREQUESTS_GEN##

CREATE TRIGGER JOINGROUPREQUESTS_GEN_TRIG FOR JOINGROUPREQUESTS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINGROUPREQUESTS_GEN, 1); 
END##

/*[JOINGROUPRESPONSES]*/

CREATE TABLE JOINGROUPRESPONSES (
    SID                BIGINT NOT NULL,
    USERID             INTEGER NOT NULL,
    GROUPID            INTEGER NOT NULL,
    CONTENT            VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    AGREE              SMALLINT NOT NULL,
    SENDER             INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE JOINGROUPRESPONSES ADD CONSTRAINT PK_JOINGROUPRESPONSES PRIMARY KEY (SID)##

CREATE INDEX JOINGROUPRESPONSES_IDX_USERID ON JOINGROUPRESPONSES (USERID)##

CREATE INDEX JOINGROUPRESPONSES_IDX_GROUPID ON JOINGROUPRESPONSES (GROUPID)##

CREATE INDEX JOINGROUPRESPONSES_IDX_SENDER ON JOINGROUPRESPONSES (SENDER)##

CREATE INDEX JOINGROUPRESPONSES_IDX_SENDTIME ON JOINGROUPRESPONSES (SENDTIME)##

CREATE GENERATOR JOINGROUPRESPONSES_GEN##

CREATE TRIGGER JOINGROUPRESPONSES_GEN_TRIG FOR JOINGROUPRESPONSES 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINGROUPRESPONSES_GEN, 1); 
END##

/*[ROOMS]*/

CREATE TABLE ROOMS (
    ROOMID             INTEGER NOT NULL,
    ROOMNAME           VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    INTRODUCTION       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    NOTICE             VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    JOINSETTING        SMALLINT NOT NULL,
    CREATORID          INTEGER NOT NULL,
    OWNERID            INTEGER NOT NULL,
    OWNERUSERTYPE      SMALLINT NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ROOMS ADD CONSTRAINT PK_ROOMS PRIMARY KEY (ROOMID)##

CREATE INDEX ROOMS_IDX_CREATORID ON ROOMS (CREATORID)##

CREATE INDEX ROOMS_IDX_OWNERID ON ROOMS (OWNERID)##

CREATE INDEX ROOMS_IDX_OWNERUSERTYPE ON ROOMS (OWNERUSERTYPE)##

CREATE INDEX ROOMS_IDX_CREATETIME ON ROOMS (CREATETIME)##

/*[ROOMMEMBERS]*/

CREATE TABLE ROOMMEMBERS (
    MEMBERID           INTEGER NOT NULL,
    ROOMID             INTEGER NOT NULL,
    ISMANAGER          SMALLINT NOT NULL,
    LASTRECVMSGSID     BIGINT NOT NULL,
    MSGHINTSETTING     SMALLINT NOT NULL,
    MODIFYCARDBYMNG    SMALLINT NOT NULL,
    CARD_NAME          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_SEX           SMALLINT NOT NULL,
    CARD_NUMBER        VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_EMAIL         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CARD_REMARK        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,    
    JOINTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE ROOMMEMBERS ADD CONSTRAINT PK_ROOMMEMBERS PRIMARY KEY (MEMBERID,ROOMID)##

CREATE INDEX ROOMMEMBERS_IDX_ISMANAGER ON ROOMMEMBERS (ISMANAGER)##

CREATE INDEX ROOMMEMBERS_IDX_LASTRECVMSGSID ON ROOMMEMBERS (LASTRECVMSGSID)##

CREATE INDEX ROOMMEMBERS_IDX_JOINTIME ON ROOMMEMBERS (JOINTIME)##

/*[ROOMSHAREFILES]*/

CREATE TABLE ROOMSHAREFILES (
    SID                VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    MEMBERID           INTEGER NOT NULL,
    ROOMID             INTEGER NOT NULL,
    FILENAMEORDIR      VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    ISDIR              SMALLINT NOT NULL,
    ALLFILECOUNT       INTEGER NOT NULL,
    ALLFILESIZE        BIGINT NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ROOMSHAREFILES ADD CONSTRAINT PK_ROOMSHAREFILES PRIMARY KEY (SID)##

CREATE INDEX ROOMSHAREFILES_IDX_MEMBERID ON ROOMSHAREFILES (MEMBERID)##

CREATE INDEX ROOMSHAREFILES_IDX_ROOMID ON ROOMSHAREFILES (ROOMID)##

CREATE INDEX ROOMSHAREFILES_IDX_CREATETIME ON ROOMSHAREFILES (CREATETIME)##

/*[OFFROOMMSGS]*/

CREATE TABLE OFFROOMMSGS (
    SID                BIGINT NOT NULL,
    ROOMID             INTEGER NOT NULL,
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    MSGLEVEL           SMALLINT NOT NULL,
    CONTENT            VARCHAR(8192) CHARACTER SET UNICODE_FSS NOT NULL,
    FONT               VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_USERID      INTEGER NOT NULL,
    SENDER_NICKNAME    VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_ACTUALNAME  VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE OFFROOMMSGS ADD CONSTRAINT PK_OFFROOMMSGS PRIMARY KEY (SID)##

CREATE INDEX OFFROOMMSGS_IDX_ROOMID ON OFFROOMMSGS (ROOMID)##

CREATE INDEX OFFROOMMSGS_IDX_MSGID ON OFFROOMMSGS (MSGID)##

CREATE INDEX OFFROOMMSGS_IDX_SENDER_USERID ON OFFROOMMSGS (SENDER_USERID)##

CREATE INDEX OFFROOMMSGS_IDX_SENDER_NICKNAME ON OFFROOMMSGS (SENDER_NICKNAME)##

CREATE INDEX OFFROOMMSGS_IDX_SENDTIME ON OFFROOMMSGS (SENDTIME)##

CREATE GENERATOR OFFROOMMSGS_GEN##

CREATE TRIGGER OFFROOMMSGS_GEN_TRIG FOR OFFROOMMSGS
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(OFFROOMMSGS_GEN, 1);
END##

/*[OFFROOMSUPEROBJS]*/

CREATE TABLE OFFROOMSUPEROBJS (
    SID                BIGINT NOT NULL,    
    ROOMID             INTEGER NOT NULL,
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    FROMUSERID         INTEGER NOT NULL,
    SUPEROBJCODE       VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    LOCALFILENAME      VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE OFFROOMSUPEROBJS ADD CONSTRAINT PK_OFFROOMSUPEROBJS PRIMARY KEY (SID)##

CREATE INDEX OFFROOMSUPEROBJS_IDX_ROOMID ON OFFROOMSUPEROBJS (ROOMID)##

CREATE INDEX OFFROOMSUPEROBJS_IDX_MSGID ON OFFROOMSUPEROBJS (MSGID)##

CREATE INDEX OFFROOMSUPEROBJS_IDX_CREATETIME ON OFFROOMSUPEROBJS (CREATETIME)##

CREATE GENERATOR OFFROOMSUPEROBJS_GEN##

CREATE TRIGGER OFFROOMSUPEROBJS_GEN_TRIG FOR OFFROOMSUPEROBJS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(OFFROOMSUPEROBJS_GEN, 1); 
END##

/*[JOINROOMREQUESTS]*/

CREATE TABLE JOINROOMREQUESTS (
    SID                BIGINT NOT NULL,
    USERID             INTEGER NOT NULL,
    ROOMID             INTEGER NOT NULL,
    CONTENT            VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER             INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE JOINROOMREQUESTS ADD CONSTRAINT PK_JOINROOMREQUESTS PRIMARY KEY (SID)##

CREATE INDEX JOINROOMREQUESTS_IDX_USERID ON JOINROOMREQUESTS (USERID)##

CREATE INDEX JOINROOMREQUESTS_IDX_ROOMID ON JOINROOMREQUESTS (ROOMID)##

CREATE INDEX JOINROOMREQUESTS_IDX_SENDER ON JOINROOMREQUESTS (SENDER)##

CREATE INDEX JOINROOMREQUESTS_IDX_SENDTIME ON JOINROOMREQUESTS (SENDTIME)##

CREATE GENERATOR JOINROOMREQUESTS_GEN##

CREATE TRIGGER JOINROOMREQUESTS_GEN_TRIG FOR JOINROOMREQUESTS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINROOMREQUESTS_GEN, 1); 
END##

/*[JOINROOMRESPONSES]*/

CREATE TABLE JOINROOMRESPONSES (
    SID                BIGINT NOT NULL,
    USERID             INTEGER NOT NULL,
    ROOMID             INTEGER NOT NULL,
    CONTENT            VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    AGREE              SMALLINT NOT NULL,
    SENDER             INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE JOINROOMRESPONSES ADD CONSTRAINT PK_JOINROOMRESPONSES PRIMARY KEY (SID)##

CREATE INDEX JOINROOMRESPONSES_IDX_USERID ON JOINROOMRESPONSES (USERID)##

CREATE INDEX JOINROOMRESPONSES_IDX_ROOMID ON JOINROOMRESPONSES (ROOMID)##

CREATE INDEX JOINROOMRESPONSES_IDX_SENDER ON JOINROOMRESPONSES (SENDER)##

CREATE INDEX JOINROOMRESPONSES_IDX_SENDTIME ON JOINROOMRESPONSES (SENDTIME)##

CREATE GENERATOR JOINROOMRESPONSES_GEN##

CREATE TRIGGER JOINROOMRESPONSES_GEN_TRIG FOR JOINROOMRESPONSES 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINROOMRESPONSES_GEN, 1); 
END##

/*[WEBVISITORTOTAL]*/

CREATE TABLE WEBVISITORTOTAL (
    KEYNAME            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    KEYVALUE           INTEGER NOT NULL
)##

ALTER TABLE WEBVISITORTOTAL ADD CONSTRAINT PK_WEBVISITORTOTAL PRIMARY KEY (KEYNAME)##

CREATE INDEX WEBVISITORTOTAL_IDX_KEYVALUE ON WEBVISITORTOTAL (KEYVALUE)##

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

/*[WEBVISITORS]*/

CREATE TABLE WEBVISITORS (
    VISITORID          INTEGER NOT NULL,
    VISITORCODE        VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    USERID             INTEGER NOT NULL,
    SRCURL             VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    CLIENTIPADDR       VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CLIENTLOCATION     VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CLIENTOS           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    WEBBROWSER         VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    REMARKNAME         VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    REMARKTEXT         VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    FLAG               SMALLINT NOT NULL,
    ISACTIVE           SMALLINT NOT NULL,
    LOGINTIMES         INTEGER NOT NULL,
    LASTLOGINTIME      TIMESTAMP NOT NULL,
    SENDMSGS           INTEGER NOT NULL,
    RECVMSGS           INTEGER NOT NULL,
    LEAVEMSGS          INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE WEBVISITORS ADD CONSTRAINT PK_WEBVISITORS PRIMARY KEY (VISITORID, USERID)##

CREATE INDEX WEBVISITORS_IDX_ISACTIVE ON WEBVISITORS (ISACTIVE)##

CREATE INDEX WEBVISITORS_IDX_CREATETIME ON WEBVISITORS (CREATETIME)##

/*[WEBVISITORMESSAGES]*/

CREATE TABLE WEBVISITORMESSAGES (
    SID                BIGINT NOT NULL,
    VISITORID          INTEGER NOT NULL,
    USERID             INTEGER NOT NULL,
    SENDERISVISITOR    SMALLINT NOT NULL,
    CONTENT            VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE WEBVISITORMESSAGES ADD CONSTRAINT PK_WEBVISITORMESSAGES PRIMARY KEY (SID)##

CREATE INDEX WEBVISITORMESSAGES_IDX_V_ID ON WEBVISITORMESSAGES (VISITORID)##

CREATE INDEX WEBVISITORMESSAGES_IDX_U_ID ON WEBVISITORMESSAGES (USERID)##

CREATE INDEX WEBVISITORMESSAGES_IDX_CTIME ON WEBVISITORMESSAGES (CREATETIME)##

CREATE GENERATOR WEBVISITORMESSAGES_GEN##

CREATE TRIGGER WEBVISITORMESSAGES_GEN_TRIG FOR WEBVISITORMESSAGES 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(WEBVISITORMESSAGES_GEN, 1); 
END##

/*[WEBAPPS]*/

CREATE TABLE WEBAPPS (
    APPID              INTEGER NOT NULL,
    FORUSERID          INTEGER NOT NULL,
    FORUSERTYPE        SMALLINT NOT NULL,
    APPNAME            VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CATEGORY           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    INTRODUCTION       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    APPIMAGE           VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    APPURL             VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    METHOD             SMALLINT NOT NULL,
    POSTDATA           VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    POPUP              INTEGER NOT NULL,
    CLIENTWEBBROWSER   SMALLINT NOT NULL,
    WEBBROWSERWIDTH    INTEGER NOT NULL,
    WEBBROWSERHEIGHT   INTEGER NOT NULL,
    SHORTCUT           SMALLINT NOT NULL,
    ORDERVALUE         INTEGER NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE WEBAPPS ADD CONSTRAINT PK_WEBAPPS PRIMARY KEY (APPID)##

CREATE INDEX WEBAPPS_IDX_FORUSERID ON WEBAPPS (FORUSERID)##

CREATE INDEX WEBAPPS_IDX_FORUSERTYPE ON WEBAPPS (FORUSERTYPE)##

CREATE INDEX WEBAPPS_IDX_ORDERVALUE ON WEBAPPS (ORDERVALUE)##

CREATE INDEX WEBAPPS_IDX_CREATETIME ON WEBAPPS (CREATETIME)##

/*[RECENTITEMS]*/

CREATE TABLE RECENTITEMS (
    SID                BIGINT NOT NULL,
    USERID             INTEGER NOT NULL,
    ITEMTYPE           SMALLINT NOT NULL,
    ITEMVALUE          VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE RECENTITEMS ADD CONSTRAINT PK_RECENTITEMS PRIMARY KEY (SID)##

CREATE INDEX RECENTITEMS_IDX_USERID ON RECENTITEMS (USERID)##

CREATE INDEX RECENTITEMS_IDX_CREATETIME ON RECENTITEMS (CREATETIME)##

CREATE GENERATOR RECENTITEMS_GEN##

CREATE TRIGGER RECENTITEMS_GEN_TRIG FOR RECENTITEMS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(RECENTITEMS_GEN, 1); 
END##

/*[SYSMSGS]*/

CREATE TABLE SYSMSGS (
    SYSMSGID           BIGINT NOT NULL, 
    TITLE              VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL, 
    CATEGORY           VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL, 
    CONTENT            VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL, 
    LINK               VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL, 
    ATTACHFILE         VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL, 
    FORUSERTYPE        SMALLINT NOT NULL,     
    FORDEPARTID        INTEGER NOT NULL, 
    FORDEPARTNAME      VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDTONUM          INTEGER NOT NULL,
    SENDTODETAIL       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    FORONLINEUSERS     SMALLINT NOT NULL,
    NEEDREADED         SMALLINT NOT NULL,
    NEEDFEEDBACK       SMALLINT NOT NULL,
    READEDCOUNT        INTEGER NOT NULL,
    FEEDBACKCOUNT      INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL 
)##

ALTER TABLE SYSMSGS ADD CONSTRAINT PK_SYSMSGS PRIMARY KEY (SYSMSGID)##

CREATE INDEX SYSMSGS_IDX_FORUSERTYPE ON SYSMSGS (FORUSERTYPE)##

CREATE INDEX SYSMSGS_IDX_FORDEPARTID ON SYSMSGS (FORDEPARTID)##

CREATE INDEX SYSMSGS_IDX_FORONLINEUSERS ON SYSMSGS (FORONLINEUSERS)##

CREATE INDEX SYSMSGS_IDX_SENDTIME ON SYSMSGS (SENDTIME)##

/*[SYSMSGDETAILS]*/

CREATE TABLE SYSMSGDETAILS (
    SYSMSGID           BIGINT NOT NULL, 
    USERID             INTEGER NOT NULL,
    READED             SMALLINT NOT NULL,
    FEEDBACK           SMALLINT NOT NULL,
    FEEDBACKTEXT       VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL, 
    SENDTIME           TIMESTAMP NOT NULL 
)##

ALTER TABLE SYSMSGDETAILS ADD CONSTRAINT PK_SYSMSGDETAILS PRIMARY KEY (SYSMSGID, USERID)##

CREATE INDEX SYSMSGDETAILS_IDX_SENDTIME ON SYSMSGDETAILS (SENDTIME)##

/*[SENDSMS]*/

CREATE TABLE SENDSMS (
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    USERID             INTEGER NOT NULL,
    MOBILE             VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CONTENT            VARCHAR(4000) CHARACTER SET UNICODE_FSS NOT NULL,
    RECEIVER           INTEGER NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL 
)##

ALTER TABLE SENDSMS ADD CONSTRAINT PK_SENDSMS PRIMARY KEY (MSGID)##

CREATE INDEX SENDSMS_IDX_USERID ON SENDSMS (USERID)##

CREATE INDEX SENDSMS_IDX_RECEIVER ON SENDSMS (RECEIVER)##

CREATE INDEX SENDSMS_IDX_SENDTIME ON SENDSMS (SENDTIME)##

/*[RECVSMS]*/

CREATE TABLE RECVSMS (
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    USERID             INTEGER NOT NULL,
    MOBILE             VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CONTENT            VARCHAR(4000) CHARACTER SET UNICODE_FSS NOT NULL,
    HAVEREAD           SMALLINT NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL 
)##

ALTER TABLE RECVSMS ADD CONSTRAINT PK_RECVSMS PRIMARY KEY (MSGID)##

CREATE INDEX RECVSMS_IDX_USERID ON RECVSMS (USERID)##

CREATE INDEX RECVSMS_IDX_HAVEREAD ON RECVSMS (HAVEREAD)##

CREATE INDEX RECVSMS_IDX_SENDTIME ON RECVSMS (SENDTIME)##

/*[ADMINS]*/

CREATE TABLE ADMINS (
    ADMINNAME          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    ADMINRIGHTS        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    ADMINPWD           VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,    
    DESCRIPTION        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    LOGINED            SMALLINT NOT NULL,
    ISLOCK             SMALLINT NOT NULL,
    LASTLOGINTIME      TIMESTAMP,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ADMINS ADD CONSTRAINT PK_ADMINS PRIMARY KEY (ADMINNAME)##

CREATE INDEX ADMINS_IDX_ADMINPWD ON ADMINS (ADMINPWD)##

CREATE INDEX ADMINS_IDX_LOGINED ON ADMINS (LOGINED)##

CREATE INDEX ADMINS_IDX_ISLOCK ON ADMINS (ISLOCK)##

CREATE INDEX ADMINS_IDX_CREATETIME ON ADMINS (CREATETIME)##

/*[ADMINLOGS]*/

CREATE TABLE ADMINLOGS (
    SID                BIGINT NOT NULL,
    ADMINNAME          VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    DESCRIPTION        VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    IPADDR             VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE ADMINLOGS ADD CONSTRAINT PK_ADMINLOGS PRIMARY KEY (SID)##

CREATE INDEX ADMINLOGS_IDX_ADMINNAME ON ADMINLOGS (ADMINNAME)##

CREATE INDEX ADMINLOGS_IDX_CREATETIME ON ADMINLOGS (CREATETIME)##

CREATE GENERATOR ADMINLOGS_GEN##

CREATE TRIGGER ADMINLOGS_GEN_TRIG FOR ADMINLOGS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(ADMINLOGS_GEN, 1); 
END##

/*[OFFLINEFILES]*/

CREATE TABLE OFFLINEFILES (
    SID                VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    SENDER_USERID      INTEGER NOT NULL,
    RECEIVER_USERID    INTEGER NOT NULL,
    FILENAMEORDIR      VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    ISDIR              SMALLINT NOT NULL,
    ALLFILECOUNT       INTEGER NOT NULL,
    ALLFILESIZE        BIGINT NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE OFFLINEFILES ADD CONSTRAINT PK_OFFLINEFILES PRIMARY KEY (SID)##

CREATE INDEX OFFLINEFILES_IDX_S_UID ON OFFLINEFILES (SENDER_USERID)##

CREATE INDEX OFFLINEFILES_IDX_R_UID ON OFFLINEFILES (RECEIVER_USERID)##

CREATE INDEX OFFLINEFILES_IDX_C_TIME ON OFFLINEFILES (CREATETIME)##

/*[OFFLINEMESSAGES]*/

CREATE TABLE OFFLINEMESSAGES (
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    USERID             INTEGER NOT NULL,
    MSGTYPE            SMALLINT NOT NULL,
    MSGLEVEL           SMALLINT NOT NULL,
    CONTENT            VARCHAR(8192) CHARACTER SET UNICODE_FSS NOT NULL,
    FONT               VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_USERID      INTEGER NOT NULL,
    SENDER_NICKNAME    VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER_ACTUALNAME  VARCHAR(20) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDTIME           TIMESTAMP NOT NULL
)##

ALTER TABLE OFFLINEMESSAGES ADD CONSTRAINT PK_OFFLINEMESSAGES PRIMARY KEY (MSGID, USERID)##

CREATE INDEX OFFLINEMESSAGES_IDX_S_UID ON OFFLINEMESSAGES (SENDER_USERID)##

CREATE INDEX OFFLINEMESSAGES_IDX_S_NNAME ON OFFLINEMESSAGES (SENDER_NICKNAME)##

CREATE INDEX OFFLINEMESSAGES_IDX_S_TIME ON OFFLINEMESSAGES (SENDTIME)##

/*[OFFLINESUPEROBJECTS]*/

CREATE TABLE OFFLINESUPEROBJECTS (
    SID                BIGINT NOT NULL,    
    MSGID              VARCHAR(50) CHARACTER SET ASCII NOT NULL,
    FROMUSERID         INTEGER NOT NULL,
    SUPEROBJECTCODE    VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    LOCALFILENAME      VARCHAR(1024) CHARACTER SET UNICODE_FSS NOT NULL,
    CREATETIME         TIMESTAMP NOT NULL
)##

ALTER TABLE OFFLINESUPEROBJECTS ADD CONSTRAINT PK_OFFLINESUPEROBJECTS PRIMARY KEY (SID)##

CREATE INDEX OFFLINESUPEROBJECTS_IDX_MSGID ON OFFLINESUPEROBJECTS (MSGID)##

CREATE INDEX OFFLINESUPEROBJECTS_IDX_F_UID ON OFFLINESUPEROBJECTS (FROMUSERID)##

CREATE INDEX OFFLINESUPEROBJECTS_IDX_C_TIME ON OFFLINESUPEROBJECTS (CREATETIME)##

CREATE GENERATOR OFFLINESUPEROBJECTS_GEN##

CREATE TRIGGER OFFLINESUPEROBJECTS_GEN_TRIG FOR OFFLINESUPEROBJECTS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(OFFLINESUPEROBJECTS_GEN, 1); 
END##

/*[JOINREQUESTS]*/

CREATE TABLE JOINREQUESTS (
    SID               BIGINT NOT NULL,
    USERID            INTEGER NOT NULL,
    CONTENT           VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    SENDER            INTEGER NOT NULL,
    SENDTIME          TIMESTAMP NOT NULL
)##

ALTER TABLE JOINREQUESTS ADD CONSTRAINT PK_JOINREQUESTS PRIMARY KEY (SID)##

CREATE INDEX JOINREQUESTS_IDX_USERID ON JOINREQUESTS (USERID)##

CREATE INDEX JOINREQUESTS_IDX_SENDER ON JOINREQUESTS (SENDER)##

CREATE INDEX JOINREQUESTS_IDX_SENDTIME ON JOINREQUESTS (SENDTIME)##

CREATE GENERATOR JOINREQUESTS_GEN##

CREATE TRIGGER JOINREQUESTS_GEN_TRIG FOR JOINREQUESTS 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINREQUESTS_GEN, 1); 
END##

/*[JOINRESPONSES]*/

CREATE TABLE JOINRESPONSES (
    SID               BIGINT NOT NULL,
    USERID            INTEGER NOT NULL,
    CONTENT           VARCHAR(255) CHARACTER SET UNICODE_FSS NOT NULL,
    AGREE             SMALLINT NOT NULL,
    SENDER            INTEGER NOT NULL,
    SENDTIME          TIMESTAMP NOT NULL
)##

ALTER TABLE JOINRESPONSES ADD CONSTRAINT PK_JOINRESPONSES PRIMARY KEY (SID)##

CREATE INDEX JOINRESPONSES_IDX_USERID ON JOINRESPONSES (USERID)##

CREATE INDEX JOINRESPONSES_IDX_SENDER ON JOINRESPONSES (SENDER)##

CREATE INDEX JOINRESPONSES_IDX_SENDTIME ON JOINRESPONSES (SENDTIME)##

CREATE GENERATOR JOINRESPONSES_GEN##

CREATE TRIGGER JOINRESPONSES_GEN_TRIG FOR JOINRESPONSES 
BEFORE INSERT 
AS
BEGIN 
  IF (NEW.SID IS NULL) 
    THEN NEW.SID = GEN_ID(JOINRESPONSES_GEN, 1); 
END##

/*[PUSH]*/

CREATE TABLE PUSH (
    PUSHID          INTEGER NOT NULL,
    USERID          INTEGER NOT NULL,
    CLIENTID        VARCHAR(50) CHARACTER SET UNICODE_FSS NOT NULL,
    DEVICE          VARCHAR(50) CHARACTER SET UNICODE_FSS,
    OS              SMALLINT NOT NULL,
    BADGE           INTEGER,
    IOSDEVICETOKEN  VARCHAR(100) CHARACTER SET UNICODE_FSS,
    ISACTIVE        SMALLINT,
    CREATETIME      TIMESTAMP,
    NEEDPUSH        SMALLINT
)##

ALTER TABLE PUSH ADD CONSTRAINT PK_PUSH PRIMARY KEY (PUSHID)##


/*----------------------------------------------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 OR ALTER PROCEDURE GETACCOUNTSECTIONS 
returns (
    out_beginuserid integer,
    out_enduserid integer,
    out_createtime timestamp)
as
begin
  for select BEGINUSERID, ENDUSERID, CREATETIME from ACCOUNTSECTIONS 
    where 1 = 1 order by CREATETIME into
    :out_beginuserid, :out_enduserid, :out_createtime do
  begin
    suspend;
  end
end##

/* ADDACCOUNTS */

CREATE OR ALTER PROCEDURE ADDACCOUNTS (
    in_beginuserid integer,
    in_enduserid integer)
returns (
    out_errorcode integer,
    out_completed integer)
as
  declare variable var_tempuserid integer;
  declare variable var_curuserid integer;
  declare variable var_section varchar(50) character set unicode_fss;
begin
  out_errorcode = 0;

  out_completed = 0;

  if ((in_beginuserid <= 0) or (in_enduserid < in_beginuserid)) then 
  begin
    out_errorcode = 1;
    suspend;
    exit;
  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)) then
  begin
    out_errorcode = 2;
    suspend;
    exit;
  end

  insert into ACCOUNTSECTIONS(BEGINUSERID, ENDUSERID, CREATETIME) values(:in_beginuserid, :in_enduserid, CURRENT_TIMESTAMP);

  var_section = cast(in_beginuserid as varchar(20)) || '-' || cast(in_enduserid as varchar(20));

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

      out_completed = out_completed + 1;
    end
    var_curuserid = var_curuserid + 1;
  end

  suspend;
end##

/* REMOVEACCOUNTS */

CREATE OR ALTER PROCEDURE REMOVEACCOUNTS (
    in_beginuserid integer,
    in_enduserid integer)
as
begin
  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);
end##

/* REGISTERACCOUNT */

CREATE OR ALTER PROCEDURE REGISTERACCOUNT (
    in_ipaddr varchar(50) character set unicode_fss)
returns (
    out_errorcode integer,
    out_userid integer,
    out_registed smallint,
    out_registertime timestamp,
    out_registeripaddr varchar(50) character set unicode_fss,
    out_section varchar(50) character set unicode_fss,
    out_createtime timestamp)
as
  declare variable var_24hoursbefore timestamp;
  declare variable var_now timestamp;
  declare variable var_regnumthisday integer;
  declare variable var_newuserid integer;
begin
  out_errorcode = 0;

  var_now = CURRENT_TIMESTAMP;
  var_24hoursbefore = addHour(var_now, -24);

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

  if (var_regnumthisday >= 10) then
  begin
    out_errorcode = 1;
    suspend;
    exit;
  end

  select first 1 USERID from ACCOUNTS 
      where (REGISTED = 0) order by RANDOMVALUE into :var_newuserid;

  if (not (var_newuserid is null)) then
  begin
    update ACCOUNTS set REGISTED = 1, REGISTERTIME = CURRENT_TIMESTAMP, REGISTERIPADDR = :in_ipaddr where USERID = :var_newuserid;

    select USERID, REGISTED, REGISTERTIME, REGISTERIPADDR, SECTION, CREATETIME from ACCOUNTS 
        where USERID = :var_newuserid into
        :out_userid, :out_registed, :out_registertime, :out_registeripaddr, :out_section, :out_createtime;
  end
  else
  begin
    out_errorcode = 2;
  end

  suspend;
end##

/* COUNTACCOUNTS */

CREATE OR ALTER PROCEDURE COUNTACCOUNTS 
returns (
    out_count integer,
    out_registed smallint)
as
begin
  select count(*) from ACCOUNTS where 1 = 1 into :out_count;
  select count(*) from ACCOUNTS where (REGISTED = 1) into :out_registed;
  suspend;
end##

/* SEARCHACCOUNTS */

CREATE OR ALTER PROCEDURE SEARCHACCOUNTS (
    in_beginuserid integer,
    in_enduserid integer,
    in_pagesize integer,
    in_pageindex integer)
returns (
    out_maxpageindex integer,
    out_userid integer,
    out_registed smallint,
    out_registertime timestamp,
    out_registeripaddr varchar(50) character set unicode_fss,
    out_section varchar(50) character set unicode_fss,
    out_createtime timestamp)
as
  declare variable var_accountcount integer;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
begin
  if ((in_beginuserid > 0) and (in_enduserid > 0)) then
  begin
      select count(*) from ACCOUNTS where 
        USERID >= :in_beginuserid and USERID <= :in_enduserid into :var_accountcount;
  end
  else
  begin
      select count(*) from ACCOUNTS where 1 = 1 into :var_accountcount;
  end

  out_maxpageindex = var_accountcount / :in_pagesize;
  if (Mod(var_accountcount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

  if (out_maxpageindex <= 0) then out_maxpageindex = 1;

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

  if ((in_beginuserid > 0) and (in_enduserid > 0)) then
  begin
    var_where_sql = 
      '(USERID >= ' || cast(in_beginuserid as varchar(20)) || ')' ||
      ' and (USERID <= ' || cast(in_enduserid as varchar(20)) || ')';
  end
  else
  begin
    var_where_sql = '1 = 1';
  end

  var_where_sql = var_where_sql || ' order by USERID DESC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select USERID, REGISTED, REGISTERTIME, REGISTERIPADDR, SECTION, CREATETIME' ||
      ' from ACCOUNTS where ' || var_where_sql into 
      :out_userid, :out_registed, :out_registertime, :out_registeripaddr, :out_section, :out_createtime
    do
  begin
    suspend;
  end
end##

/* ADDDEPARTMENT */

CREATE OR ALTER PROCEDURE ADDDEPARTMENT (
    in_departname varchar(50) character set unicode_fss,
    in_parentdepartid integer)
returns (
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentdepartid integer,
    out_createtime timestamp)
as
  declare variable var_newdepartid integer;
  declare variable var_ordervalue integer;
begin
  select max(DEPARTID) from DEPARTMENTS into :var_newdepartid;
  if (var_newdepartid is null) then var_newdepartid = 0;
  var_newdepartid = var_newdepartid + 1;

  select max(ORDERVALUE) from DEPARTMENTS into :var_ordervalue;
  if (var_ordervalue is null) then var_ordervalue = 0;
  var_ordervalue = var_ordervalue + 1;

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

  select DEPARTID, DEPARTNAME, ORDERVALUE, PARENTDEPARTID, CREATETIME
       from DEPARTMENTS where DEPARTID = :var_newdepartid into :out_departid, :out_departname,
       :out_ordervalue, :out_parentdepartid, :out_createtime;

  suspend;
end##

/* ADDDEPARTMENTEX */

CREATE OR ALTER PROCEDURE ADDDEPARTMENTEX (
    in_departid integer,
    in_departname varchar(50) character set unicode_fss,
    in_ordervalue integer,
    in_parentdepartid integer)
returns (
    out_errorcode integer,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentdepartid integer,
    out_createtime timestamp)
as
begin
  out_errorcode = 0;

  if (exists(select * from DEPARTMENTS where DEPARTID = :in_departid)) then
  begin
    out_errorcode = 1;
    suspend;
    exit;
  end

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

  select DEPARTID, DEPARTNAME, ORDERVALUE, PARENTDEPARTID, CREATETIME
       from DEPARTMENTS where DEPARTID = :in_departid into :out_departid, 
       :out_departname, :out_ordervalue, :out_parentdepartid, :out_createtime;

  suspend;
end##

/* MODIFYDEPARTMENT */

CREATE OR ALTER PROCEDURE MODIFYDEPARTMENT (
    in_departid integer,
    in_departname varchar(50) character set unicode_fss)
as
begin
  update DEPARTMENTS set DEPARTNAME = :in_departname where DEPARTID = :in_departid;
  update USERS set DEPARTNAME = :in_departname where DEPARTID = :in_departid;
end##

/* MOVEDEPARTMENT */

CREATE OR ALTER PROCEDURE MOVEDEPARTMENT (
    in_departid integer,
    in_parentdepartid integer)
as
    declare variable var_ordervalue integer;
begin
  select max(ORDERVALUE) from DEPARTMENTS where PARENTDEPARTID = :in_parentdepartid into :var_ordervalue;
  if (var_ordervalue is null) then
  begin
    var_ordervalue = 0;
  end
  else
  begin
    var_ordervalue = var_ordervalue + 1;
  end
  
  update DEPARTMENTS set PARENTDEPARTID = :in_parentdepartid, ORDERVALUE = :var_ordervalue where DEPARTID = :in_departid;
end##

/* SETDEPARTMENTORDERVALUE */

CREATE OR ALTER PROCEDURE SETDEPARTMENTORDERVALUE (
    in_departid integer,
    in_ordervalue integer)
as
begin
  update DEPARTMENTS set ORDERVALUE = :in_ordervalue where DEPARTID = :in_departid;
end##

/* GETDEPARTMENT */

CREATE OR ALTER PROCEDURE GETDEPARTMENT (
    in_departid integer)
returns (
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentdepartid integer,
    out_createtime timestamp)
as
begin
  select DEPARTID, DEPARTNAME, ORDERVALUE, PARENTDEPARTID, CREATETIME
       from DEPARTMENTS where DEPARTID = :in_departid into :out_departid, 
       :out_departname, :out_ordervalue, :out_parentdepartid, :out_createtime;

  suspend;
end##

/* GETDEPARTMENTS */

CREATE OR ALTER PROCEDURE GETDEPARTMENTS (
    in_parentdepartid integer)
returns (
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentdepartid integer,
    out_createtime timestamp)
as
begin
  if (in_parentdepartid >= 0) then
  begin
      for select DEPARTID, DEPARTNAME, ORDERVALUE, PARENTDEPARTID, CREATETIME
           from DEPARTMENTS where PARENTDEPARTID = :in_parentdepartid
           order by PARENTDEPARTID ASC, ORDERVALUE ASC into :out_departid, 
           :out_departname, :out_ordervalue, :out_parentdepartid, :out_createtime
        do
      begin
        suspend;
      end
  end
  else
  begin
      for select DEPARTID, DEPARTNAME,  ORDERVALUE, PARENTDEPARTID, CREATETIME
           from DEPARTMENTS where 1 = 1 order by PARENTDEPARTID ASC, ORDERVALUE ASC into :out_departid, 
           :out_departname, :out_ordervalue, :out_parentdepartid, :out_createtime
        do
      begin
        suspend;
      end
  end
end##

/* GETDEPARTMENTUSERS */

CREATE OR ALTER PROCEDURE GETDEPARTMENTUSERS (
    in_departid integer)
returns (
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_userposition integer,

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,
    
    out_modifytime timestamp)
as
begin
  if (in_departid > 0) then
  begin
      for select 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, USERPOSITION, SMSTOTAL, SMSDAYLIMIT, SMSDAYSTART, SMSDAYSENDED, SMSTOTALSENDED, MODIFYTIME
           from USERS where DEPARTID = :in_departid order by USERPOSITION ASC into       
           :out_userid, :out_username, :out_usertype, :out_canfound, :out_nickname, :out_signature, :out_actualname, :out_sex, :out_age,
           :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, :out_city, :out_area, 
           :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
           :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
           :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
           :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, :out_facefile, 
           :out_photofile, :out_loginstatus, :out_userposition, :out_smstotal, :out_smsdaylimit, 
	   :out_smsdaystart, :out_smsdaysended, :out_smstotalsended, :out_modifytime
        do
      begin
        suspend;
      end

  end
  else
  begin
      for select 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, USERPOSITION, SMSTOTAL, SMSDAYLIMIT, SMSDAYSTART, SMSDAYSENDED, SMSTOTALSENDED, MODIFYTIME
           from USERS where DEPARTID > 0 order by USERPOSITION ASC into       
           :out_userid, :out_username, :out_usertype, :out_canfound, :out_nickname, :out_signature, :out_actualname, :out_sex, :out_age,
           :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, :out_city, :out_area, 
           :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
           :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
           :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
           :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, :out_facefile, 
           :out_photofile, :out_loginstatus, :out_userposition, :out_smstotal, :out_smsdaylimit, 
	   :out_smsdaystart, :out_smsdaysended, :out_smstotalsended, :out_modifytime
        do
      begin
        suspend;
      end
  end
end##

/* EXISTSUSER */

CREATE OR ALTER PROCEDURE EXISTSUSER (
    in_userid integer)
returns (
    out_errorcode integer)
as
begin
  out_errorcode = 0;
  if (exists(select * from USERS where USERID = :in_userid)) then
  begin
    out_errorcode = 1;
  end
  suspend;
end##

/* ADDUSEREX */

CREATE OR ALTER PROCEDURE ADDUSEREX (
    in_userid integer,
    in_username varchar(50) character set unicode_fss,
    in_usertype smallint,
    in_canfound smallint,

    in_nickname varchar(20) character set unicode_fss,
    in_signature varchar(255) character set unicode_fss,
    in_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    in_telephone varchar(20) character set unicode_fss,
    in_telephone_short varchar(20) character set unicode_fss,
    in_telephone2 varchar(20) character set unicode_fss,
    in_telephone2_short varchar(20) character set unicode_fss,
    in_mobile varchar(20) character set unicode_fss,
    in_mobile_short varchar(20) character set unicode_fss,
    in_mobile2 varchar(20) character set unicode_fss,
    in_mobile2_short varchar(20) character set unicode_fss,
    in_fax varchar(20) character set unicode_fss,
    in_qq varchar(50) character set unicode_fss,
    in_weixin varchar(50) character set unicode_fss,
    in_weibo varchar(50) character set unicode_fss,
    in_email varchar(50) character set unicode_fss,
    in_roomname varchar(50) character set unicode_fss,
    in_roomnumber varchar(50) character set unicode_fss,
    in_idnumber varchar(50) character set unicode_fss,
    in_carnumber varchar(50) character set unicode_fss,
    in_homepage varchar(80) character set unicode_fss,
    in_departid integer,
    in_departname varchar(50) character set unicode_fss,
    in_jobtitle varchar(50) character set unicode_fss,
    in_jobnumber varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,

    in_facefile varchar(255) character set unicode_fss,
    in_photofile varchar(255) character set unicode_fss,

    in_pwd varchar(255) character set unicode_fss,
    in_salt varchar(6) character set unicode_fss,
	
    in_smstotal integer,
    in_smsdaylimit integer
    )
returns (
    out_errorcode integer)
as
  declare variable var_userposition integer;
begin
  out_errorcode = 0;
  
  if (exists(select * from users where userid = :in_userid)) then
  begin
    out_errorcode = 1; /*--userid exists--*/
    suspend;
    EXIT;
  end

  if (in_username != '') then
  begin
    if (exists(select * from users where username = :in_username)) then
    begin
      out_errorcode = 2; /*--username exists--*/
      suspend;
      EXIT;
    end
  end
  
  var_userposition = 0;
  if ((in_usertype = 1) and (in_departid > 0)) then
  begin
    select max(USERPOSITION) from users where DEPARTID = :in_departid into :var_userposition;
	if (var_userposition is null) then var_userposition = -1;
	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,
    CURRENT_TIMESTAMP,
    0,
    0,

    CURRENT_TIMESTAMP,       /*modifytime*/
    CURRENT_TIMESTAMP        /*createtime*/
    );

  suspend;
end##

/* CHANGEUSERDEPARTMENT */

CREATE OR ALTER PROCEDURE CHANGEUSERDEPARTMENT (
    in_userid integer,
    in_departid integer,
    in_departname varchar(50) character set unicode_fss)
as
begin
  update USERS set 
    DEPARTID = :in_departid,
    DEPARTNAME = :in_departname,
    MODIFYTIME = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* GETWHOJOINMEIDS */

CREATE OR ALTER PROCEDURE GETWHOJOINMEIDS (
    in_userid integer)
returns (
    out_userid integer)
as
begin
  for select distinct USERID from CONTACTS 
    where USERID = :in_userid or CONTACTID = :in_userid into :out_userid do 
  begin
    suspend;
  end
end##

/* ISJOINME */

CREATE OR ALTER PROCEDURE ISJOINME (
    in_userid integer,
    in_contactid integer)
returns (
    out_status integer)
as
begin
  out_status = 0;
  if (exists(select * from CONTACTS 
    where USERID = :in_contactid or CONTACTID = :in_userid)) then 
  begin
    out_status = 1;
  end
  suspend;
end##

/* CHANGELOGINSTATUS */

CREATE OR ALTER PROCEDURE CHANGELOGINSTATUS (
    in_userid integer,
    in_loginstatus smallint)
as 
begin
  update USERS set 
    LOGINSTATUS = :in_loginstatus
    where USERID = :in_userid;
end##

/* VALIDATEPASSWORD */

CREATE OR ALTER PROCEDURE VALIDATEPASSWORD (
    in_userid integer,
    in_password varchar(255) CHARACTER SET UNICODE_FSS)
returns (
    out_errorcode integer)
as
begin
  out_errorcode = 0;

  if (not exists(select * from USERS where USERID = :in_userid and PWD = :in_password)) then
  begin
    out_errorcode = 1; /*---(not find user) or (password error)---*/
    suspend;
    EXIT;
  end

  suspend;
end##

/* CHANGEPASSWORD */

CREATE OR ALTER PROCEDURE CHANGEPASSWORD (
    in_userid integer,
    in_oldpwd varchar(255) CHARACTER SET UNICODE_FSS,
    in_newpwd varchar(255) CHARACTER SET UNICODE_FSS)
returns (
    out_errorcode integer)
as
begin
  out_errorcode = 0;

  if (not exists(select * from USERS where USERID = :in_userid and PWD = :in_oldpwd)) then
  begin
    out_errorcode = 1; /*---(not find user) or (oldpwd error)---*/
    suspend;
    EXIT;
  end

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

  suspend;
end##

/* RESETPASSWORD */

CREATE OR ALTER PROCEDURE RESETPASSWORD (
    in_userid integer,
    in_newpwd varchar(255) CHARACTER SET UNICODE_FSS)
as
begin
  update USERS set PWD = :in_newpwd where USERID = :in_userid;
end##

/* MODIFYUSER */

CREATE OR ALTER PROCEDURE MODIFYUSER (
    in_userid integer,
    in_canfound smallint,
    in_nickname varchar(20) character set unicode_fss,
    in_signature varchar(255) character set unicode_fss,
    in_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    in_telephone varchar(20) character set unicode_fss,
    in_telephone_short varchar(20) character set unicode_fss,
    in_telephone2 varchar(20) character set unicode_fss,
    in_telephone2_short varchar(20) character set unicode_fss,
    in_mobile varchar(20) character set unicode_fss,
    in_mobile_short varchar(20) character set unicode_fss,
    in_mobile2 varchar(20) character set unicode_fss,
    in_mobile2_short varchar(20) character set unicode_fss,
    in_fax varchar(20) character set unicode_fss,
    in_qq varchar(50) character set unicode_fss,
    in_weixin varchar(50) character set unicode_fss,
    in_weibo varchar(50) character set unicode_fss,
    in_email varchar(50) character set unicode_fss,
    in_roomname varchar(50) character set unicode_fss,
    in_roomnumber varchar(50) character set unicode_fss,
    in_idnumber varchar(50) character set unicode_fss,
    in_carnumber varchar(50) character set unicode_fss,
    in_homepage varchar(80) character set unicode_fss,
    in_departid integer,
    in_departname varchar(50) character set unicode_fss,
    in_jobtitle varchar(50) character set unicode_fss,
    in_jobnumber varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss)
as
begin
  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 = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* CHANGESIGNATURE */

CREATE OR ALTER PROCEDURE CHANGESIGNATURE (
    in_userid integer,
    in_signature varchar(255) CHARACTER SET UNICODE_FSS)
as
begin
  update USERS set 
    SIGNATURE = :in_signature,
    MODIFYTIME = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* CHANGEUSERINFO */

CREATE OR ALTER PROCEDURE CHANGEUSERINFO (
    in_userid integer,

    in_nickname varchar(20) character set unicode_fss,
    in_signature varchar(255) character set unicode_fss,
    in_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    in_telephone varchar(20) character set unicode_fss,
    in_telephone_short varchar(20) character set unicode_fss,
    in_telephone2 varchar(20) character set unicode_fss,
    in_telephone2_short varchar(20) character set unicode_fss,
    in_mobile varchar(20) character set unicode_fss,
    in_mobile_short varchar(20) character set unicode_fss,
    in_mobile2 varchar(20) character set unicode_fss,
    in_mobile2_short varchar(20) character set unicode_fss,
    in_fax varchar(20) character set unicode_fss,
    in_qq varchar(50) character set unicode_fss,
    in_weixin varchar(50) character set unicode_fss,
    in_weibo varchar(50) character set unicode_fss,
    in_email varchar(50) character set unicode_fss,
    in_roomname varchar(50) character set unicode_fss,
    in_roomnumber varchar(50) character set unicode_fss,
    in_idnumber varchar(50) character set unicode_fss,
    in_carnumber varchar(50) character set unicode_fss,

    in_homepage varchar(80) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss)
as
begin
  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 = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* CHANGEFACEFILE */

CREATE OR ALTER PROCEDURE CHANGEFACEFILE (
    in_userid integer,
    in_facefile varchar(255) character set unicode_fss)
as
begin
  update USERS set 
    FACEFILE = :in_facefile,
    MODIFYTIME = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* CHANGEPHOTOFILE */

CREATE OR ALTER PROCEDURE CHANGEPHOTOFILE (
    in_userid integer,
    in_photofile varchar(255) character set unicode_fss)
as
begin
  update USERS set 
    PHOTOFILE = :in_photofile,
    MODIFYTIME = CURRENT_TIMESTAMP
    where USERID = :in_userid;
end##

/* CHANGELINKDEPART */

CREATE OR ALTER PROCEDURE CHANGELINKDEPART (
    in_userid integer,
    in_linkdepartid integer)
as
begin
  update USERS set 
    LINKDEPARTID = :in_linkdepartid
    where USERID = :in_userid;
end##

/* CHANGEUSERPOSITION */

CREATE OR ALTER PROCEDURE CHANGEUSERPOSITION (
    in_userid integer,
    in_userposition integer)
as
begin
  update USERS set 
    USERPOSITION = :in_userposition
    where USERID = :in_userid;
end##

/* GETUSERBYID */

CREATE OR ALTER PROCEDURE GETUSERBYID (
    in_userid integer)
returns (
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_logintimes bigint,
    out_lastlogintime timestamp,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_lastclientipaddr    varchar(50) character set unicode_fss,
    out_lastclientlocation    varchar(50) character set unicode_fss,

    out_pwd varchar(255) character set unicode_fss,
    out_salt varchar(6) character set unicode_fss,
    out_token varchar(255) character set unicode_fss,
    out_tokenupdatetime timestamp,
    out_locked smallint,

    out_onlyfindmebyid smallint,
    out_joinsetting smallint,
    out_joinquestion varchar(50) character set unicode_fss,
    out_joinanswer varchar(50) character set unicode_fss,
    
    out_lastrecvsysmsgs timestamp,
    out_linkdepartid integer,
    out_userposition integer,

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,

    out_modifytime timestamp,
    out_createtime timestamp)
as
begin
  select 
    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

    from USERS where USERID = :in_userid into 
    
    :out_userid, 
    :out_username, 
    :out_usertype,
    :out_canfound,

    :out_nickname, 
    :out_signature, 
    :out_actualname,
    :out_sex, 
    :out_age, 
    :out_birth_year, 
    :out_birth_month, 
    :out_birth_day, 
    :out_country, 
    :out_province, 
    :out_city, 
    :out_area, 
    :out_address,
    :out_telephone,
    :out_telephone_short,
    :out_telephone2,
    :out_telephone2_short,
    :out_mobile,
    :out_mobile_short,
    :out_mobile2,
    :out_mobile2_short,
    :out_fax,
    :out_qq,
    :out_weixin,
    :out_weibo,
    :out_email,
    :out_roomname,
    :out_roomnumber,
    :out_idnumber,
    :out_carnumber,
    :out_homepage,
    :out_departid,
    :out_departname,
    :out_jobtitle, 
    :out_jobnumber,
    :out_introduction, 

    :out_facefile, 
    :out_photofile, 

    :out_loginstatus, 
    :out_logintimes, 
    :out_lastlogintime,
    :out_clientipaddr, 
    :out_clientlocation, 
    :out_lastclientipaddr, 
    :out_lastclientlocation, 

    :out_pwd, 
    :out_salt,
    :out_token, 
    :out_tokenupdatetime, 
    :out_locked, 

    :out_onlyfindmebyid, 
    :out_joinsetting, 
    :out_joinquestion, 
    :out_joinanswer, 
    
    :out_lastrecvsysmsgs,
    :out_linkdepartid, 
    :out_userposition,	

    :out_smstotal,
    :out_smsdaylimit,
    :out_smsdaystart,
    :out_smsdaysended,    
    :out_smstotalsended,

    :out_modifytime,
    :out_createtime;

  suspend;
end##

/* GETUSERBYNAME */

CREATE OR ALTER PROCEDURE GETUSERBYNAME (
    in_username varchar(50) character set unicode_fss)
returns (
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_logintimes bigint,
    out_lastlogintime timestamp,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_lastclientipaddr    varchar(50) character set unicode_fss,
    out_lastclientlocation    varchar(50) character set unicode_fss,

    out_pwd varchar(255) character set unicode_fss,
    out_salt varchar(6) character set unicode_fss,
    out_token varchar(255) character set unicode_fss,
    out_tokenupdatetime timestamp,
    out_locked smallint,

    out_onlyfindmebyid smallint,
    out_joinsetting smallint,
    out_joinquestion varchar(50) character set unicode_fss,
    out_joinanswer varchar(50) character set unicode_fss,
    
    out_lastrecvsysmsgs timestamp,
    out_linkdepartid integer,   
    out_userposition integer,	

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,

    out_modifytime timestamp,
    out_createtime timestamp)
as
begin
  select 
    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

    from USERS where USERNAME = :in_username into 
    
    :out_userid, 
    :out_username, 
    :out_usertype,
    :out_canfound,

    :out_nickname, 
    :out_signature, 
    :out_actualname,
    :out_sex, 
    :out_age, 
    :out_birth_year, 
    :out_birth_month, 
    :out_birth_day, 
    :out_country, 
    :out_province, 
    :out_city, 
    :out_area, 
    :out_address,
    :out_telephone,
    :out_telephone_short,
    :out_telephone2,
    :out_telephone2_short,
    :out_mobile,
    :out_mobile_short,
    :out_mobile2,
    :out_mobile2_short,
    :out_fax,
    :out_qq,
    :out_weixin,
    :out_weibo,
    :out_email,
    :out_roomname,
    :out_roomnumber,
    :out_idnumber,
    :out_carnumber,
    :out_homepage,
    :out_departid,
    :out_departname,
    :out_jobtitle, 
    :out_jobnumber,
    :out_introduction, 

    :out_facefile, 
    :out_photofile, 

    :out_loginstatus, 
    :out_logintimes, 
    :out_lastlogintime,
    :out_clientipaddr, 
    :out_clientlocation, 
    :out_lastclientipaddr, 
    :out_lastclientlocation, 

    :out_pwd, 
    :out_salt,
    :out_token, 
    :out_tokenupdatetime, 
    :out_locked, 

    :out_onlyfindmebyid, 
    :out_joinsetting, 
    :out_joinquestion, 
    :out_joinanswer, 
    
    :out_lastrecvsysmsgs,
    :out_linkdepartid,   
    :out_userposition,	

    :out_smstotal,
    :out_smsdaylimit,
    :out_smsdaystart,
    :out_smsdaysended,    
    :out_smstotalsended,    

    :out_modifytime,
    :out_createtime;

  suspend;
end##

/* GETUSERBYTOKEN */

CREATE OR ALTER PROCEDURE GETUSERBYTOKEN (
    in_token varchar(255) character set unicode_fss)
returns (
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_logintimes bigint,
    out_lastlogintime timestamp,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_lastclientipaddr    varchar(50) character set unicode_fss,
    out_lastclientlocation    varchar(50) character set unicode_fss,

    out_pwd varchar(255) character set unicode_fss,
    out_salt varchar(6) character set unicode_fss,
    out_token varchar(255) character set unicode_fss,
    out_tokenupdatetime timestamp,
    out_locked smallint,

    out_onlyfindmebyid smallint,
    out_joinsetting smallint,
    out_joinquestion varchar(50) character set unicode_fss,
    out_joinanswer varchar(50) character set unicode_fss,
    
    out_lastrecvsysmsgs timestamp,
    out_linkdepartid integer,
	out_userposition integer,

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,    

    out_modifytime timestamp,
    out_createtime timestamp)
as
begin
  select 
    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

    from USERS where TOKEN = :in_token into 
    
    :out_userid, 
    :out_username, 
    :out_usertype,
    :out_canfound,

    :out_nickname, 
    :out_signature, 
    :out_actualname,
    :out_sex, 
    :out_age, 
    :out_birth_year, 
    :out_birth_month, 
    :out_birth_day, 
    :out_country, 
    :out_province, 
    :out_city, 
    :out_area, 
    :out_address,
    :out_telephone,
    :out_telephone_short,
    :out_telephone2,
    :out_telephone2_short,
    :out_mobile,
    :out_mobile_short,
    :out_mobile2,
    :out_mobile2_short,
    :out_fax,
    :out_qq,
    :out_weixin,
    :out_weibo,
    :out_email,
    :out_roomname,
    :out_roomnumber,
    :out_idnumber,
    :out_carnumber,
    :out_homepage,
    :out_departid,
    :out_departname,
    :out_jobtitle, 
    :out_jobnumber,
    :out_introduction, 

    :out_facefile, 
    :out_photofile, 

    :out_loginstatus, 
    :out_logintimes, 
    :out_lastlogintime,
    :out_clientipaddr, 
    :out_clientlocation, 
    :out_lastclientipaddr, 
    :out_lastclientlocation, 

    :out_pwd, 
    :out_salt,
    :out_token, 
    :out_tokenupdatetime, 
    :out_locked, 

    :out_onlyfindmebyid, 
    :out_joinsetting, 
    :out_joinquestion, 
    :out_joinanswer, 
    
    :out_lastrecvsysmsgs,
    :out_linkdepartid,
	:out_userposition,

    :out_smstotal,
    :out_smsdaylimit,
    :out_smsdaystart,
    :out_smsdaysended,    
    :out_smstotalsended,    

    :out_modifytime,
    :out_createtime;

  suspend;
end##

/* LOCKUSER */

CREATE OR ALTER PROCEDURE LOCKUSER (
    in_userid integer,
    in_value smallint)
as
begin
  update USERS set 
    LOCKED = :in_value
    where USERID = :in_userid;
end##

/* LOGIN */

CREATE OR ALTER PROCEDURE LOGIN (
    in_logintype integer not null,
    in_loginname varchar(50) character set unicode_fss not null,
    in_pwd varchar(255) character set unicode_fss not null,
    in_newtoken varchar(255) character set unicode_fss not null,
    in_loginstatus integer not null,
    in_clientipaddr varchar(50) character set unicode_fss not null,
    in_clientlocation varchar(50) character set unicode_fss not null)
returns (
    out_errorcode integer,
    out_userid integer,
    out_token varchar(255) character set unicode_fss)
as 
  declare variable var_pwd varchar(255) character set unicode_fss;
  declare variable var_tokenupdatetime timestamp;
  declare variable var_lock smallint;
begin
  out_errorcode = 0;
  out_userid = -1;
  out_token = '';

  var_pwd = '';
  var_lock = 0;

  if ( (trim(in_loginname) = '') or
    ((in_logintype != 2) and (trim(in_newtoken) = '')) )then
  begin
    out_errorcode = 1; /*--invalid params--*/
    suspend;
    EXIT;
  end

  if (in_logintype = 0) then /*----login by out_userid----*/
  begin
    select USERID,PWD,TOKEN,TOKENUPDATETIME,LOCKED from USERS 
    where USERID = CAST(trim(:in_loginname) as integer) 
    into :out_userid,:var_pwd,:out_token,:var_tokenupdatetime,:var_lock;
  end
  else if (in_logintype = 1) then /*----login by username----*/
  begin
    if (exists(select * from USERS where USERNAME = trim(:in_loginname))) then
    begin
        select USERID,PWD,TOKEN,TOKENUPDATETIME,LOCKED from USERS 
            where USERNAME = trim(:in_loginname)
            into :out_userid,:var_pwd,:out_token,:var_tokenupdatetime,:var_lock;
    end
    else
    begin
        select USERID,PWD,TOKEN,TOKENUPDATETIME,LOCKED from USERS 
            where ACTUALNAME = trim(:in_loginname) or NICKNAME = trim(:in_loginname)
            into :out_userid,:var_pwd,:out_token,:var_tokenupdatetime,:var_lock;
    end
  end
  else if (in_logintype = 2) then /*----login by token----*/
  begin
    select USERID,PWD,TOKEN,TOKENUPDATETIME,LOCKED from USERS 
    where TOKEN = trim(:in_loginname) 
    into :out_userid,:var_pwd,:out_token,:var_tokenupdatetime,:var_lock;
  end
  
  if (out_userid = -1) then
  begin
    out_errorcode = 2; /*--not find user--*/
    suspend;
    EXIT;
  end

  if (var_lock != 0) then
  begin
    out_errorcode = 3; /*--user lock--*/
    suspend;
    EXIT;
  end

  if (in_logintype != 2) then /*---login by userid,username---*/
  begin
    if ( (trim(in_pwd) != '') and (var_pwd != trim(in_pwd)) ) then
    begin
      out_errorcode = 4; /*--password error--*/
      suspend;
      EXIT;
    end
    
    /*---update token---*/
    if ( (out_token != '') and not (var_tokenupdatetime is null) and 
      (addMinute(var_tokenupdatetime, 5) >= CURRENT_TIMESTAMP) ) then
    begin
      update USERS set 
        TOKENUPDATETIME = CURRENT_TIMESTAMP
        where USERID = :out_userid;
    end
    else
    begin
      update USERS set 
        TOKEN = trim(:in_newtoken),
        TOKENUPDATETIME = CURRENT_TIMESTAMP
        where USERID = :out_userid;
    end
  end
  else /*---login by token---*/
  begin
    if (out_token != trim(in_loginname)) then
    begin
      out_errorcode = 5; /*--token error--*/
      suspend;
      EXIT;
    end

    if ( not (var_tokenupdatetime is null) and 
      (addMinute(var_tokenupdatetime, 5) < CURRENT_TIMESTAMP) ) then
    begin
      out_errorcode = 6; /*--token timeout--*/
      suspend;
      EXIT;
    end

    update USERS set 
      TOKENUPDATETIME = CURRENT_TIMESTAMP
      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 = CURRENT_TIMESTAMP,
    CLIENTIPADDR = :in_clientipaddr,
    CLIENTLOCATION = :in_clientlocation
    where USERID = :out_userid;

  suspend;
end##

/* LOGOUT */

CREATE OR ALTER PROCEDURE LOGOUT (
    in_userid integer not null)
as 
begin
  update USERS set LOGINSTATUS = 0, TOKEN = '' where USERID = :in_userid;
end##

/* REMOVEUSER */

CREATE OR ALTER PROCEDURE REMOVEUSER (
    in_userid integer)
as
  declare variable var_groupid integer;
  declare variable var_roomid integer;
begin
  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)) then
  begin
      for select GROUPID from GROUPS where OWNERID = :in_userid
        into :var_groupid do
      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;        
      end

      delete from GROUPS where OWNERID = :in_userid;
  end

  delete from ROOMMEMBERS where MEMBERID = :in_userid;
  if (exists(select * from ROOMS where OWNERID = :in_userid)) then
  begin
      for select ROOMID from ROOMS where OWNERID = :in_userid
        into :var_roomid do
      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;        
      end

      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;
end##

/* RESETLOGINSTATUS */

CREATE OR ALTER PROCEDURE RESETLOGINSTATUS (
    in_userid integer)
as 
begin
  if (in_userid = -1) then
  begin
    update USERS set LOGINSTATUS = 0 where 1 = 1;
  end
  else
  begin
    update USERS set LOGINSTATUS = 0 where USERID = :in_userid;
  end
end##

/* UPDATESMSSETTING */

CREATE OR ALTER PROCEDURE UPDATESMSSETTING (
    in_userid integer,
    in_smstotal integer,
    in_smsdaylimit integer)
as 
begin
    update USERS set SMSTOTAL = :in_smstotal, SMSDAYLIMIT = :in_smsdaylimit where USERID = :in_userid;
end##

/* UPDATESMSSENDCOUNT */

CREATE OR ALTER PROCEDURE UPDATESMSSENDCOUNT (
    in_userid integer,
    in_smsdaystart timestamp,
    in_smsdaysended integer,
    in_smstotalsended integer)
as 
begin
  update USERS set SMSDAYSTART = :in_smsdaystart, SMSDAYSENDED = :in_smsdaysended, 
    SMSTOTALSENDED = :in_smstotalsended where USERID = :in_userid;
end##

/* UPDATEUSERJOINSETTING */

CREATE OR ALTER PROCEDURE UPDATEUSERJOINSETTING (
    in_userid integer,
    in_onlyfindmebyid smallint,
    in_joinsetting smallint,
    in_joinquestion varchar(50) CHARACTER SET UNICODE_FSS,
    in_joinanswer varchar(50) CHARACTER SET UNICODE_FSS)
as
begin
  update USERS set 
    ONLYFINDMEBYID = :in_onlyfindmebyid,
    JOINSETTING = :in_joinsetting,
    JOINQUESTION = :in_joinquestion,
    JOINANSWER = :in_joinanswer
    where USERID = :in_userid;
end##

/* GETUSERJOINSETTING */

CREATE OR ALTER PROCEDURE GETUSERJOINSETTING (
    in_userid integer)
returns (
    out_joinsetting smallint,
    out_joinquestion varchar(50) CHARACTER SET UNICODE_FSS)
as
begin
  select JOINSETTING, JOINQUESTION 
    from USERS where USERID = :in_userid into
    :out_joinsetting, :out_joinquestion;
  suspend;
end##

/* VALIDATEUSERJOINQUESTION */

CREATE OR ALTER PROCEDURE VALIDATEUSERJOINQUESTION (
    in_userid integer,
    in_joinsetting smallint,
    in_joinquestion varchar(50) CHARACTER SET UNICODE_FSS,
    in_joinanswer varchar(50) CHARACTER SET UNICODE_FSS)
returns (
    out_errorcode integer)
as
begin
  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))) then
  begin
    out_errorcode = 1;
  end

  suspend;
end##

/* GETPWDPROTECTION */

CREATE OR ALTER PROCEDURE GETPWDPROTECTION (
    in_userid integer)
returns (
    out_question1 varchar(50) CHARACTER SET UNICODE_FSS,
    out_question2 varchar(50) CHARACTER SET UNICODE_FSS,
    out_question3 varchar(50) CHARACTER SET UNICODE_FSS)
as
begin
  select QUESTION1, QUESTION2, QUESTION3 from PWDPROTECTION 
    where USERID = :in_userid into :out_question1, :out_question2, :out_question3;

  suspend;
end##

/* SETPWDPROTECTION */

CREATE OR ALTER PROCEDURE SETPWDPROTECTION (
    in_userid integer,
    in_question1 varchar(50) CHARACTER SET UNICODE_FSS,
    in_answer1 varchar(50) CHARACTER SET UNICODE_FSS,
    in_question2 varchar(50) CHARACTER SET UNICODE_FSS,
    in_answer2 varchar(50) CHARACTER SET UNICODE_FSS,
    in_question3 varchar(50) CHARACTER SET UNICODE_FSS,
    in_answer3 varchar(50) CHARACTER SET UNICODE_FSS)
as
begin
  if (not exists(select * from PWDPROTECTION where USERID = :in_userid)) then
  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,
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP);
  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 = CURRENT_TIMESTAMP
      where USERID = :in_userid;
  end
end##

/* VALIDATEPWDPROTECTION */

CREATE OR ALTER PROCEDURE VALIDATEPWDPROTECTION (
    in_userid integer,
    in_answer1 varchar(50) CHARACTER SET UNICODE_FSS,
    in_answer2 varchar(50) CHARACTER SET UNICODE_FSS,
    in_answer3 varchar(50) CHARACTER SET UNICODE_FSS)
returns (
    out_errorcode integer)
as
begin
  if (not exists(select * from PWDPROTECTION where (USERID = :in_userid) and 
    (ANSWER1 = :in_answer1) and (ANSWER2 = :in_answer2) and (ANSWER3 = :in_answer3) )) then
  begin
    out_errorcode = 1;
  end
  else
  begin
    out_errorcode = 0;
  end

  suspend;
end##

/* SEARCHUSERS */

CREATE OR ALTER PROCEDURE SEARCHUSERS (
    in_userid integer,
    in_param_userid integer,
    in_param_username varchar(50) character set unicode_fss,
    in_param_nickname varchar(20) character set unicode_fss,
    in_param_actualname varchar(20) character set unicode_fss,
    in_param_departname varchar(50) character set unicode_fss,
    in_param_jobnumber varchar(20) character set unicode_fss,
    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 integer,
    in_pageindex integer)
returns (
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint
)
as
  declare variable var_usertype smallint;
  declare variable var_canfound smallint;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
  declare variable var_where_sql_temp varchar(200) character set unicode_fss;
begin
  var_usertype = null;
  var_canfound = null;

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

  if ((var_usertype is null) or
    (var_canfound is null)) then
  begin
    suspend;
    EXIT;
  end

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

      var_where_sql = var_where_sql || ' or ((USERTYPE = 1) and (CANFOUND = 1)) )';
  end
  else if (var_usertype = 1) then /*EnterpriseUsers*/
  begin
      var_where_sql = '( (USERTYPE = 1)';

      if (var_canfound = 1) then
      begin
        var_where_sql = var_where_sql || ' or (USERTYPE = 0)';
      end

      var_where_sql = var_where_sql || ')';
  end
  else
  begin
    suspend;
    EXIT;
  end

  if (in_param_userid > 0) then
  begin
    var_where_sql_temp = '(USERID = ' || cast(in_param_userid as varchar(20)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end
  else
  begin
    var_where_sql_temp = '(ONLYFINDMEBYID = 0)';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_username <> '') then
  begin
    var_where_sql_temp = '(USERNAME = ''' || in_param_username || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_nickname <> '') then
  begin
    var_where_sql_temp = '(NICKNAME = ''' || in_param_nickname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_actualname <> '') then
  begin
    var_where_sql_temp = '(ACTUALNAME = ''' || in_param_actualname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_departname <> '') then
  begin
    var_where_sql_temp = '(DEPARTNAME = ''' || in_param_departname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_jobnumber <> '') then
  begin
    var_where_sql_temp = '(JOBNUMBER = ''' || in_param_jobnumber || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_country <> -1) then
  begin
    var_where_sql_temp = '(COUNTRY = ' || cast(in_param_country as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_province <> -1) then
  begin
    var_where_sql_temp = '(PROVINCE = ' || cast(in_param_province as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_city <> -1) then
  begin
    var_where_sql_temp = '(CITY = ' || cast(in_param_city as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_area <> -1) then
  begin
    var_where_sql_temp = '(AREA = ' || cast(in_param_area as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_sex <> -1) then
  begin
    var_where_sql_temp = '(SEX = ' || cast(in_param_sex as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

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

    if (var_where_sql_temp <> '') then
      var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_online = 1) then
  begin
    var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 5))';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  var_where_sql = var_where_sql || ' order by USERID ASC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) 
    || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select USERID, USERNAME, USERTYPE, 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 ' ||
    'from USERS where ' || var_where_sql into 
    :out_userid, :out_username, :out_usertype,
    :out_nickname, :out_signature, :out_actualname, :out_sex, :out_age, 
    :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, :out_city, :out_area, 
    :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
    :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
    :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
    :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, :out_facefile, :out_photofile, :out_loginstatus
  do
  begin
    suspend;
  end
end##

/* SEARCHPUBLICUSERS */

CREATE OR ALTER PROCEDURE SEARCHPUBLICUSERS (
    in_param_beginuserid integer,
    in_param_enduserid integer,
    in_param_username varchar(50) character set unicode_fss,
    in_param_nickname varchar(20) character set unicode_fss,
    in_param_actualname varchar(20) character set unicode_fss,
    in_param_sex smallint,
    in_param_age smallint,
    in_param_online smallint,
    in_param_locked smallint,
    in_pagesize integer,
    in_pageindex integer)
returns (
    out_allusercount integer,
    out_onlineusercount integer,
    out_maxpageindex integer,
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_logintimes bigint,
    out_lastlogintime timestamp,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_lastclientipaddr varchar(50) character set unicode_fss,
    out_lastclientlocation varchar(50) character set unicode_fss,

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,

    out_locked smallint,
    out_createtime timestamp
)
as
  declare variable var_where_sql varchar(1024) character set unicode_fss;
  declare variable var_where_sql_temp varchar(200) character set unicode_fss;
begin
  var_where_sql = '(USERTYPE = 0)';

  if (in_param_beginuserid > 0) then
  begin
    var_where_sql_temp = '(USERID >= ' || cast(in_param_beginuserid as varchar(20)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if ((in_param_enduserid > 0) and (in_param_enduserid >= in_param_beginuserid)) then
  begin
    var_where_sql_temp = '(USERID <= ' || cast(in_param_beginuserid as varchar(20)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_username <> '') then
  begin
    var_where_sql_temp = '(USERNAME = ''' || in_param_username || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_nickname <> '') then
  begin
    var_where_sql_temp = '(NICKNAME = ''' || in_param_nickname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_actualname <> '') then
  begin
    var_where_sql_temp = '(ACTUALNAME = ''' || in_param_actualname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_sex <> -1) then
  begin
    var_where_sql_temp = '(SEX = ' || cast(in_param_sex as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

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

    if (var_where_sql_temp <> '') then
      var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_online = 1) then
  begin
    var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 6))';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_locked = 1) then
  begin
    var_where_sql_temp = '(LOCKED = 1)';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  EXECUTE STATEMENT 'select count(*) from USERS where ' || var_where_sql into :out_allusercount;
  EXECUTE STATEMENT 'select count(*) from USERS where ' || var_where_sql || ' and (LOGINSTATUS <> 0)' into :out_onlineusercount;

  out_maxpageindex = out_allusercount / :in_pagesize;
  if (Mod(out_allusercount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

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

  var_where_sql = var_where_sql || ' order by CREATETIME DESC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) 
    || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select 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, SMSTOTAL, SMSDAYLIMIT, SMSDAYSTART, SMSDAYSENDED, SMSTOTALSENDED, LOCKED, CREATETIME ' ||
    'from USERS where ' || var_where_sql into 
    :out_userid, :out_username, :out_usertype, :out_canfound, 
    :out_nickname, :out_signature, :out_actualname, :out_sex, :out_age, 
    :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, :out_city, :out_area,
    :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
    :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
    :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,    
    :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, :out_facefile, :out_photofile, 
    :out_loginstatus, :out_logintimes, :out_lastlogintime, :out_clientipaddr, :out_clientlocation, 
    :out_lastclientipaddr, :out_lastclientlocation, :out_smstotal, :out_smsdaylimit, :out_smsdaystart, 
    :out_smsdaysended, :out_smstotalsended, :out_locked, :out_createtime
  do
  begin
    suspend;
  end
end##

/* SEARCHENTERPRISEUSERS */

CREATE OR ALTER PROCEDURE SEARCHENTERPRISEUSERS (
    in_param_beginuserid integer,
    in_param_enduserid integer,
    in_param_username varchar(50) character set unicode_fss,
    in_param_nickname varchar(20) character set unicode_fss,
    in_param_actualname varchar(20) character set unicode_fss,
    in_param_departname varchar(50) character set unicode_fss,
    in_param_jobnumber varchar(20) character set unicode_fss,
    in_param_sex smallint,
    in_param_age smallint,
    in_param_online smallint,
    in_param_locked smallint,
    in_pagesize integer,
    in_pageindex integer)
returns (
    out_allusercount integer,
    out_onlineusercount integer,
    out_maxpageindex integer,
    out_userid integer,
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    out_canfound smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,

    out_loginstatus smallint,
    out_logintimes bigint,
    out_lastlogintime timestamp,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_lastclientipaddr varchar(50) character set unicode_fss,
    out_lastclientlocation varchar(50) character set unicode_fss,

    out_smstotal integer,
    out_smsdaylimit integer,
    out_smsdaystart timestamp,
    out_smsdaysended integer,    
    out_smstotalsended integer,

    out_locked smallint,
    out_createtime timestamp
)
as
  declare variable var_where_sql varchar(1024) character set unicode_fss;
  declare variable var_where_sql_temp varchar(200) character set unicode_fss;
begin
  var_where_sql = '(USERTYPE = 1)';

  if (in_param_beginuserid > 0) then
  begin
    var_where_sql_temp = '(USERID >= ' || cast(in_param_beginuserid as varchar(20)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if ((in_param_enduserid > 0) and (in_param_enduserid >= in_param_beginuserid)) then
  begin
    var_where_sql_temp = '(USERID <= ' || cast(in_param_beginuserid as varchar(20)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_username <> '') then
  begin
    var_where_sql_temp = '(USERNAME = ''' || in_param_username || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_nickname <> '') then
  begin
    var_where_sql_temp = '(NICKNAME = ''' || in_param_nickname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_actualname <> '') then
  begin
    var_where_sql_temp = '(ACTUALNAME = ''' || in_param_actualname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_departname <> '') then
  begin
    var_where_sql_temp = '(DEPARTNAME = ''' || in_param_departname || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_jobnumber <> '') then
  begin
    var_where_sql_temp = '(JOBNUMBER = ''' || in_param_jobnumber || ''')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_sex <> -1) then
  begin
    var_where_sql_temp = '(SEX = ' || cast(in_param_sex as varchar(10)) || ')';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

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

    if (var_where_sql_temp <> '') then
      var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_online = 1) then
  begin
    var_where_sql_temp = '((LOGINSTATUS <> 0) and (LOGINSTATUS <> 5))';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  if (in_param_locked = 1) then
  begin
    var_where_sql_temp = '(LOCKED = 1)';
    var_where_sql = var_where_sql || ' and ' || var_where_sql_temp;
  end

  EXECUTE STATEMENT 'select count(*) from USERS where ' || var_where_sql into :out_allusercount;
  EXECUTE STATEMENT 'select count(*) from USERS where ' || var_where_sql || ' and (LOGINSTATUS <> 0)' into :out_onlineusercount;

  out_maxpageindex = out_allusercount / :in_pagesize;
  if (Mod(out_allusercount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

  if (in_pageindex <= 0) then in_pageindex = 1;
  if (in_pageindex > out_maxpageindex) then in_pageindex = out_maxpageindex;

  var_where_sql = var_where_sql || ' order by CREATETIME DESC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select 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, SMSTOTAL, SMSDAYLIMIT, SMSDAYSTART, SMSDAYSENDED, SMSTOTALSENDED, LOCKED, CREATETIME ' ||
    'from USERS where ' || var_where_sql into 
    :out_userid, :out_username, :out_usertype, :out_canfound, 
    :out_nickname, :out_signature, :out_actualname, :out_sex, :out_age,    
    :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, :out_city, :out_area, 
    :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
    :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
    :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
    :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, :out_facefile, :out_photofile, :out_loginstatus, 
    :out_logintimes, :out_lastlogintime, :out_clientipaddr, :out_clientlocation, :out_lastclientipaddr, :out_lastclientlocation, 
    :out_smstotal, :out_smsdaylimit, :out_smsdaystart, :out_smsdaysended, :out_smstotalsended, :out_locked, :out_createtime
  do
  begin
    suspend;
  end
end##

/* COUNTPUBLICUSERS */

CREATE OR ALTER PROCEDURE COUNTPUBLICUSERS 
returns (
    out_count integer)
as
begin
  select count(*) from USERS where USERTYPE = 0 into :out_count;
  suspend;
end##

/* COUNTENTERPRISEUSERS */

CREATE OR ALTER PROCEDURE COUNTENTERPRISEUSERS 
returns (
    out_count integer)
as
begin
  select count(*) from USERS where USERTYPE = 1 into :out_count;
  suspend;
end##

/* ADDCONTACTGROUP */

CREATE OR ALTER PROCEDURE ADDCONTACTGROUP (
    in_userid integer,
    in_contactgroupname varchar(20) CHARACTER SET UNICODE_FSS)
returns(
    out_contactgroupid        integer,
    out_contactgroupname    varchar(20) character set unicode_fss,
    out_hidewhenonline        smallint,
    out_onlinewhenhide        smallint,
    out_remindmewhenlogin    smallint,
    out_nomessage        smallint,
    out_ordervalue        integer,
    out_createtime        timestamp)
as
begin
  select Max(CONTACTGROUPID) from CONTACTGROUPS where USERID = :in_userid into :out_contactgroupid;
  select Max(ORDERVALUE) from CONTACTGROUPS where USERID = :in_userid into :out_ordervalue;

  if (out_contactgroupid is null) then 
    out_contactgroupid = 101;
  else
    out_contactgroupid = out_contactgroupid + 1;

  if (out_ordervalue is null) then 
    out_ordervalue = 1;
  else
    out_ordervalue = out_ordervalue + 1;

  insert into CONTACTGROUPS(
    CONTACTGROUPID,
    USERID,
    CONTACTGROUPNAME,
    HIDEWHENONLINE,
    ONLINEWHENHIDE,
    REMINDMEWHENLOGIN,
    NOMESSAGE,
    ORDERVALUE,
    CREATETIME
  ) values(
    :out_contactgroupid,
    :in_userid,
    :in_contactgroupname,
    0,
    0,
    0,
    0,
    :out_ordervalue,
    CURRENT_TIMESTAMP
  );

  select CONTACTGROUPID, CONTACTGROUPNAME, HIDEWHENONLINE, ONLINEWHENHIDE, 
    REMINDMEWHENLOGIN, NOMESSAGE, ORDERVALUE, CREATETIME
    from CONTACTGROUPS where CONTACTGROUPID = :out_contactgroupid and USERID = :in_userid
    into out_contactgroupid, out_contactgroupname, out_hidewhenonline, out_onlinewhenhide, 
    out_remindmewhenlogin, out_nomessage, out_ordervalue, out_createtime;

  suspend;
end##

/* REMOVECONTACTGROUP */

CREATE OR ALTER PROCEDURE REMOVECONTACTGROUP (
    in_userid integer,
    in_contactgroupid integer)
as
begin
  update CONTACTS set CONTACTGROUPID = 0 
    where USERID = :in_userid and CONTACTGROUPID = :in_contactgroupid;

  delete from CONTACTGROUPS 
    where USERID = :in_userid and CONTACTGROUPID = :in_contactgroupid;
end##

/* CHANGECONTACTGROUPNAME */

CREATE OR ALTER PROCEDURE CHANGECONTACTGROUPNAME (
    in_userid integer,
    in_contactgroupid integer,
    in_contactgroupname varchar(20) CHARACTER SET UNICODE_FSS)
as
begin
  update CONTACTGROUPS set CONTACTGROUPNAME = :in_contactgroupname 
    where USERID = :in_userid and CONTACTGROUPID = :in_contactgroupid;
end##

/* CHANGECONTACTGROUPSETTING */

CREATE OR ALTER PROCEDURE CHANGECONTACTGROUPSETTING (
    in_userid integer,
    in_contactgroupid integer,
    in_hidewhenonline        smallint,
    in_onlinewhenhide        smallint,
    in_remindmewhenlogin    smallint,
    in_nomessage        smallint)
as
begin
  update CONTACTGROUPS set HIDEWHENONLINE = :in_hidewhenonline, ONLINEWHENHIDE = :in_onlinewhenhide, 
    REMINDMEWHENLOGIN = :in_remindmewhenlogin, NOMESSAGE = :in_nomessage
    where USERID = :in_userid and CONTACTGROUPID = :in_contactgroupid;
end##

/* GETCONTACTGROUPS */

CREATE OR ALTER PROCEDURE GETCONTACTGROUPS (
    in_userid integer)
returns (
    out_contactgroupid        integer,
    out_userid            integer,
    out_contactgroupname    varchar(20) character set unicode_fss,
    out_hidewhenonline        smallint,
    out_onlinewhenhide        smallint,
    out_remindmewhenlogin    smallint,
    out_nomessage        smallint,
    out_ordervalue        integer,
    out_createtime        timestamp)
as
begin
  for select CONTACTGROUPID, USERID, CONTACTGROUPNAME, HIDEWHENONLINE, ONLINEWHENHIDE, 
    REMINDMEWHENLOGIN, NOMESSAGE, ORDERVALUE, CREATETIME 
    from CONTACTGROUPS where USERID = :in_userid order by ORDERVALUE ASC 
    into :out_contactgroupid, :out_userid, :out_contactgroupname, :out_hidewhenonline, :out_onlinewhenhide, 
    :out_remindmewhenlogin, :out_nomessage, :out_ordervalue, :out_createtime 
    do 
  begin
    suspend;
  end
end##

/* ADDCONTACT */

CREATE OR ALTER PROCEDURE ADDCONTACT (
    in_userid integer,
    in_contactgroupid integer,
    in_contactid integer,
    in_remarkname varchar(20) CHARACTER SET UNICODE_FSS)
returns(
    out_contactid        integer,
    out_contactgroupid        integer,
    out_remarkname        varchar(20) character set unicode_fss,
    out_hidewhenonline        smallint,
    out_onlinewhenhide        smallint,
    out_remindmewhenlogin    smallint,
    out_nomessage        smallint,
    out_jointime        timestamp,
    
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,
    
    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,
    
    out_loginstatus smallint,
    
    out_modifytime timestamp)
as
begin
  if (exists(select * from CONTACTS where USERID = :in_userid and CONTACTID = :in_contactid)) then
  begin
    suspend;
    EXIT;
  end

  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,
    CURRENT_TIMESTAMP
  );

  select CONTACTID, CONTACTGROUPID, REMARKNAME, HIDEWHENONLINE, ONLINEWHENHIDE, REMINDMEWHENLOGIN, 
    NOMESSAGE, JOINTIME, USERNAME, USERTYPE, 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, MODIFYTIME
    from CONTACTS inner join USERS on CONTACTS.CONTACTID = USERS.USERID 
    where CONTACTS.USERID = :in_userid and CONTACTS.CONTACTID = :in_contactid
    into :out_contactid, :out_contactgroupid, :out_remarkname, :out_hidewhenonline, :out_onlinewhenhide, 
    :out_remindmewhenlogin, :out_nomessage, :out_jointime, :out_username, :out_usertype,
    :out_nickname, :out_signature, :out_actualname, 
    :out_sex, :out_age, :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, 
    :out_city, :out_area, :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
    :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
    :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
    :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, 
    :out_facefile, :out_photofile, :out_loginstatus, :out_modifytime;
  
  suspend;
end##

/* REMOVECONTACT */

CREATE OR ALTER PROCEDURE REMOVECONTACT (
    in_userid integer,
    in_contactid integer,
    in_removeotherside smallint)
as
begin
  delete from CONTACTS 
    where USERID = :in_userid and CONTACTID = :in_contactid;

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

/* MOVECONTACT */

CREATE OR ALTER PROCEDURE MOVECONTACT (
    in_userid integer,
    in_contactid integer,
    in_contactgroupid integer)
as
begin
  update CONTACTS set CONTACTGROUPID = :in_contactgroupid
    where USERID = :in_userid and CONTACTID = :in_contactid;
end##

/* MOVETOBLACKLIST */

CREATE OR ALTER PROCEDURE MOVETOBLACKLIST (
    in_userid integer,
    in_contactid integer)
as
begin
  update CONTACTS set CONTACTGROUPID = 2
    where USERID = :in_userid and CONTACTID = :in_contactid;

  delete from CONTACTS
    where USERID = :in_contactid and CONTACTID = :in_userid;
end##

/* CHANGECONTACTREMARKNAME */

CREATE OR ALTER PROCEDURE CHANGECONTACTREMARKNAME (
    in_userid integer,
    in_contactid integer,
    in_remarkname varchar(20) CHARACTER SET UNICODE_FSS)
as
begin
  update CONTACTS set REMARKNAME = :in_remarkname 
    where USERID = :in_userid and CONTACTID = :in_contactid;
end##

/* CHANGECONTACTSETTING */

CREATE OR ALTER PROCEDURE CHANGECONTACTSETTING (
    in_userid integer,
    in_contactid integer,
    in_hidewhenonline        smallint,
    in_onlinewhenhide        smallint,
    in_remindmewhenlogin    smallint,
    in_nomessage        smallint)
as
begin
  update CONTACTS set HIDEWHENONLINE = :in_hidewhenonline, ONLINEWHENHIDE = :in_onlinewhenhide, 
    REMINDMEWHENLOGIN = :in_remindmewhenlogin, NOMESSAGE = :in_nomessage
    where USERID = :in_userid and CONTACTID = :in_contactid;
end##

/* GETCONTACTSETTINGS */

CREATE OR ALTER PROCEDURE GETCONTACTSETTINGS (
    in_userid integer)
returns(
    out_contactid        integer,
    out_hidewhenonline        smallint,
    out_onlinewhenhide        smallint,
    out_remindmewhenlogin    smallint,
    out_nomessage        smallint)
as
begin
  for select CONTACTID, HIDEWHENONLINE, ONLINEWHENHIDE, 
    REMINDMEWHENLOGIN, NOMESSAGE from CONTACTS
    where USERID = :in_userid and not (HIDEWHENONLINE = 0 and 
      ONLINEWHENHIDE = 0 and REMINDMEWHENLOGIN = 0 and NOMESSAGE = 0)
    into :out_contactid, :out_hidewhenonline, :out_onlinewhenhide, 
    :out_remindmewhenlogin, :out_nomessage do
  begin
    suspend;
  end
end##

/* GETCONTACTS */

CREATE OR ALTER PROCEDURE GETCONTACTS (
    in_userid integer)
returns(
    out_contactid        integer,
    out_contactgroupid        integer,
    out_remarkname        varchar(20) character set unicode_fss,
    out_hidewhenonline        smallint,
    out_onlinewhenhide        smallint,
    out_remindmewhenlogin    smallint,
    out_nomessage        smallint,
    out_jointime        timestamp,
    
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,
    
    out_loginstatus smallint,
    
    out_modifytime timestamp)
as
begin
  for select CONTACTID, CONTACTGROUPID, REMARKNAME, HIDEWHENONLINE, ONLINEWHENHIDE, REMINDMEWHENLOGIN, 
    NOMESSAGE, JOINTIME, USERNAME, USERTYPE, 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, MODIFYTIME
    from CONTACTS inner join USERS on CONTACTS.CONTACTID = USERS.USERID where CONTACTS.USERID = :in_userid
    into :out_contactid, :out_contactgroupid, :out_remarkname, :out_hidewhenonline, :out_onlinewhenhide, 
    :out_remindmewhenlogin, :out_nomessage, :out_jointime, :out_username, :out_usertype,
    :out_nickname, :out_signature, :out_actualname, 
    :out_sex, :out_age, :out_birth_year, :out_birth_month, :out_birth_day, :out_country, :out_province, 
    :out_city, :out_area, :out_address, :out_telephone, :out_telephone_short, :out_telephone2, :out_telephone2_short, :out_mobile, 
    :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, :out_qq, :out_weixin, 
    :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_homepage,
    :out_departid, :out_departname, :out_jobtitle, :out_jobnumber, :out_introduction, 
    :out_facefile, :out_photofile, :out_loginstatus, :out_modifytime
  do 
  begin
    suspend;
  end
end##

/* ADDADDRLSTCATEGORY */

CREATE OR ALTER PROCEDURE ADDADDRLSTCATEGORY (
    in_userid integer,
    in_categoryname varchar(50) character set unicode_fss,
    in_parentcategoryid integer)
returns (
    out_categoryid integer,
    out_userid integer,
    out_categoryname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentcategoryid integer,
    out_createtime timestamp)
as
  declare variable var_newcategoryid integer;
  declare variable var_ordervalue integer;
begin
  select max(CATEGORYID) from ADDRLSTCATEGORYS where 1 = 1 into :var_newcategoryid;
  if (var_newcategoryid is null) then var_newcategoryid = 0;
  var_newcategoryid = var_newcategoryid + 1;

  select max(ORDERVALUE) from ADDRLSTCATEGORYS where USERID = :in_userid into :var_ordervalue;
  if (var_ordervalue is null) then var_ordervalue = 0;
  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,
    CURRENT_TIMESTAMP
    );

  select CATEGORYID, USERID, CATEGORYNAME, ORDERVALUE, PARENTCATEGORYID, CREATETIME
       from ADDRLSTCATEGORYS where CATEGORYID = :var_newcategoryid and USERID = :in_userid 
       into :out_categoryid, :out_userid, :out_categoryname, :out_ordervalue, :out_parentcategoryid, :out_createtime;

  suspend;
end##

/* ADDADDRLSTCATEGORYEX */

CREATE OR ALTER PROCEDURE ADDADDRLSTCATEGORYEX (
    in_userid integer,
    in_categoryid integer,
    in_categoryname varchar(50) character set unicode_fss,
    in_ordervalue integer,
    in_parentcategoryid integer)
returns (
    out_errorcode integer,
    out_categoryid integer,
    out_userid integer,
    out_categoryname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentcategoryid integer,
    out_createtime timestamp)
as
begin
  out_errorcode = 0;

  if (exists(select * from ADDRLSTCATEGORYS where CATEGORYID = :in_categoryid and USERID = :in_userid)) then
  begin
    out_errorcode = 1;
    suspend;
    exit;
  end

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

  select CATEGORYID, USERID, CATEGORYNAME, ORDERVALUE, PARENTCATEGORYID, CREATETIME
       from ADDRLSTCATEGORYS where CATEGORYID = :in_categoryid and USERID = :in_userid 
       into :out_categoryid, :out_userid, :out_categoryname, :out_ordervalue, :out_parentcategoryid, :out_createtime;

  suspend;
end##

/* MODIFYADDRLSTCATEGORY */

CREATE OR ALTER PROCEDURE MODIFYADDRLSTCATEGORY (
    in_userid integer,
    in_categoryid integer,
    in_categoryname varchar(50) character set unicode_fss)
as
begin
  update ADDRLSTCATEGORYS set CATEGORYNAME = :in_categoryname where CATEGORYID = :in_categoryid and USERID = :in_userid;
end##

/* SETADDRLSTCATEGORYORDERVALUE */

CREATE OR ALTER PROCEDURE SETADDRLSTCATEGORYORDERVALUE (
    in_userid integer,
    in_categoryid integer,
    in_ordervalue integer)
as
begin
  update ADDRLSTCATEGORYS set ORDERVALUE = :in_ordervalue where CATEGORYID = :in_categoryid and USERID = :in_userid;
end##

/* GETADDRLSTCATEGORY */

CREATE OR ALTER PROCEDURE GETADDRLSTCATEGORY (
    in_userid integer,
    in_categoryid integer)
returns (
    out_categoryid integer,
    out_userid integer,
    out_categoryname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentcategoryid integer,
    out_createtime timestamp)
as
begin
  select CATEGORYID, USERID, CATEGORYNAME, ORDERVALUE, PARENTCATEGORYID, CREATETIME
       from ADDRLSTCATEGORYS where CATEGORYID = :in_categoryid and USERID = :in_userid into :out_categoryid, 
       :out_userid, :out_categoryname, :out_ordervalue, :out_parentcategoryid, :out_createtime;

  suspend;
end##

/* GETADDRLSTCATEGORYS */

CREATE OR ALTER PROCEDURE GETADDRLSTCATEGORYS (
    in_userid integer,
    in_parentcategoryid integer)
returns (
    out_categoryid integer,
    out_userid integer,
    out_categoryname varchar(50) character set unicode_fss,
    out_ordervalue integer,
    out_parentcategoryid integer,
    out_createtime timestamp)
as
begin
  if (in_parentcategoryid >= 0) then
  begin
      for select CATEGORYID, USERID, CATEGORYNAME, ORDERVALUE, PARENTCATEGORYID, CREATETIME
           from ADDRLSTCATEGORYS where PARENTCATEGORYID = :in_parentcategoryid and USERID = :in_userid
           order by PARENTCATEGORYID ASC, ORDERVALUE ASC into :out_categoryid, :out_userid,
           :out_categoryname, :out_ordervalue, :out_parentcategoryid, :out_createtime
        do
      begin
        suspend;
      end
  end
  else
  begin
      for select CATEGORYID, USERID, CATEGORYNAME,  ORDERVALUE, PARENTCATEGORYID, CREATETIME
           from ADDRLSTCATEGORYS where USERID = :in_userid order by PARENTCATEGORYID ASC, ORDERVALUE ASC into :out_categoryid, 
           :out_userid, :out_categoryname, :out_ordervalue, :out_parentcategoryid, :out_createtime
        do
      begin
        suspend;
      end
  end
end##

/* ADDADDRLSTITEM */

CREATE OR ALTER PROCEDURE ADDADDRLSTITEM (
    in_userid integer,
    in_categoryid integer,
    in_actualname varchar(20) character set unicode_fss,
    in_sex smallint,
    in_age smallint,
    in_zipcode varchar(10) character set unicode_fss,
    in_address varchar(50) character set unicode_fss,
    in_telephone varchar(20) character set unicode_fss,
    in_telephone_short varchar(20) character set unicode_fss,
    in_telephone2 varchar(20) character set unicode_fss,
    in_telephone2_short varchar(20) character set unicode_fss,
    in_mobile varchar(20) character set unicode_fss,
    in_mobile_short varchar(20) character set unicode_fss,
    in_mobile2 varchar(20) character set unicode_fss,
    in_mobile2_short varchar(20) character set unicode_fss,
    in_fax varchar(20) character set unicode_fss,
    in_qq varchar(50) character set unicode_fss,
    in_weixin varchar(50) character set unicode_fss,
    in_weibo varchar(50) character set unicode_fss,
    in_email varchar(50) character set unicode_fss,
    in_roomname varchar(50) character set unicode_fss,
    in_roomnumber varchar(50) character set unicode_fss,
    in_idnumber varchar(50) character set unicode_fss,
    in_carnumber varchar(50) character set unicode_fss,
    in_description varchar(255) character set unicode_fss)
returns (
    out_itemid integer,
    out_categoryid integer,
    out_userid integer,
    out_actualname varchar(20) character set unicode_fss,
    out_sex smallint,
    out_age smallint,
    out_zipcode varchar(10) character set unicode_fss,
    out_address varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,   
    out_description varchar(255) character set unicode_fss,
    out_createtime timestamp)
as
  declare variable var_newitemid integer;
begin
  select max(ITEMID) from ADDRLSTITEMS where 1 = 1 into :var_newitemid;
  if (var_newitemid is null) then var_newitemid = 0;
  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,
    CURRENT_TIMESTAMP
    );

  select 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
       from ADDRLSTITEMS where ITEMID = :var_newitemid and USERID = :in_userid 
       into :out_itemid, :out_categoryid, :out_userid, :out_actualname, :out_sex, :out_age, :out_zipcode, :out_address, :out_telephone, :out_telephone_short, 
       :out_telephone2, :out_telephone2_short, :out_mobile, :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, 
       :out_qq, :out_weixin, :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_description, :out_createtime;

  suspend;
end##

/* MODIFYADDRLSTITEM */

CREATE OR ALTER PROCEDURE MODIFYADDRLSTITEM (
    in_userid integer,
    in_itemid integer,
    in_actualname varchar(20) character set unicode_fss,
    in_sex smallint,
    in_age smallint,
    in_zipcode varchar(10) character set unicode_fss,
    in_address varchar(50) character set unicode_fss,
    in_telephone varchar(20) character set unicode_fss,
    in_telephone_short varchar(20) character set unicode_fss,
    in_telephone2 varchar(20) character set unicode_fss,
    in_telephone2_short varchar(20) character set unicode_fss,
    in_mobile varchar(20) character set unicode_fss,
    in_mobile_short varchar(20) character set unicode_fss,
    in_mobile2 varchar(20) character set unicode_fss,
    in_mobile2_short varchar(20) character set unicode_fss,
    in_fax varchar(20) character set unicode_fss,
    in_qq varchar(50) character set unicode_fss,
    in_weixin varchar(50) character set unicode_fss,
    in_weibo varchar(50) character set unicode_fss,
    in_email varchar(50) character set unicode_fss,
    in_roomname varchar(50) character set unicode_fss,
    in_roomnumber varchar(50) character set unicode_fss,
    in_idnumber varchar(50) character set unicode_fss,
    in_carnumber varchar(50) character set unicode_fss,
    in_description varchar(255) character set unicode_fss)
as
begin
  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;
end##

/* CHANGEADDRLSTITEMCATEGORY */

CREATE OR ALTER PROCEDURE CHANGEADDRLSTITEMCATEGORY (
    in_userid integer,
    in_itemid integer,
    in_categoryid integer)
as
begin
  update ADDRLSTITEMS set CATEGORYID = :in_categoryid where ITEMID = :in_itemid and USERID = :in_userid;
end##

/* REMOVEADDRLSTITEM */

CREATE OR ALTER PROCEDURE REMOVEADDRLSTITEM (
    in_userid integer,
    in_itemid integer)
as
begin
  delete from ADDRLSTITEMS where ITEMID = :in_itemid and USERID = :in_userid;
end##

/* GETADDRLSTITEMS */

CREATE OR ALTER PROCEDURE GETADDRLSTITEMS (
    in_userid integer,
    in_categoryid integer)
returns (
    out_itemid integer,
    out_categoryid integer,
    out_userid integer,
    out_actualname varchar(20) character set unicode_fss,
    out_sex smallint,
    out_age smallint,
    out_zipcode varchar(10) character set unicode_fss,
    out_address varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,   
    out_description varchar(255) character set unicode_fss,
    out_createtime timestamp)
as
begin
  if (in_categoryid >= 0) then
  begin
      for select 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
        from ADDRLSTITEMS where CATEGORYID = :in_categoryid and USERID = :in_userid order by CREATETIME ASC
        into :out_itemid, :out_categoryid, :out_userid, :out_actualname, :out_sex, :out_age, :out_zipcode, :out_address, :out_telephone, :out_telephone_short, 
        :out_telephone2, :out_telephone2_short, :out_mobile, :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, 
        :out_qq, :out_weixin, :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_description, :out_createtime
        do
      begin
        suspend;
      end
  end
  else
  begin
      for select 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
        from ADDRLSTITEMS where USERID = :in_userid order by CREATETIME ASC
        into :out_itemid, :out_categoryid, :out_userid, :out_actualname, :out_sex, :out_age, :out_zipcode, :out_address, :out_telephone, :out_telephone_short, 
        :out_telephone2, :out_telephone2_short, :out_mobile, :out_mobile_short, :out_mobile2, :out_mobile2_short, :out_fax, 
        :out_qq, :out_weixin, :out_weibo, :out_email, :out_roomname, :out_roomnumber, :out_idnumber, :out_carnumber, :out_description, :out_createtime
        do
      begin
        suspend;
      end
  end
end##

/* ADDGROUP */

CREATE OR ALTER PROCEDURE ADDGROUP (
    in_groupname varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_notice varchar(255) character set unicode_fss,
    in_joinsetting smallint,
    in_creatorid integer)
returns (
    out_errorcode integer,

    out_groupid integer,
    out_groupname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp,
    
    out_me_ismanager smallint,
    out_me_lastrecvmsgsid bigint,
    out_me_msghintsetting smallint,
    out_me_modifycardbymng smallint,
    out_me_card_name varchar(20) character set unicode_fss,
    out_me_card_sex smallint,
    out_me_card_number varchar(20) character set unicode_fss,
    out_me_card_email varchar(50) character set unicode_fss,
    out_me_card_remark varchar(255) character set unicode_fss,
    out_me_jointime timestamp)
as
  declare variable var_usertype smallint;
  declare variable var_newgroupid integer;
  declare variable var_groupcount integer;
begin
  out_errorcode = 0;

  var_usertype = null;

  select USERTYPE from USERS where USERID = :in_creatorid into :var_usertype;

  if (var_usertype is null) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

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

    if (var_groupcount >= 3) then
    begin
      out_errorcode = 2;
      suspend;
      EXIT;
    end
  end

  select max(GROUPID) from GROUPS into :var_newgroupid;
  if (var_newgroupid is null) then var_newgroupid = 10000 - 1;
  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,
      CURRENT_TIMESTAMP
    );

  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,
      0,
      1,
      '',
      0,
      '',
      '',
      '',
      CURRENT_TIMESTAMP
    );

  select first 1 b.GROUPID, b.GROUPNAME, b.INTRODUCTION, b.NOTICE, b.JOINSETTING, 
       b.CREATORID, b.OWNERID, b.OWNERUSERTYPE, b.CREATETIME,
       a.ISMANAGER, a.LASTRECVMSGSID, a.MSGHINTSETTING, a.MODIFYCARDBYMNG, 
       a.CARD_NAME, a.CARD_SEX, a.CARD_NUMBER, a.CARD_EMAIL, a.CARD_REMARK, a.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 into 
       :out_groupid, :out_groupname, :out_introduction, :out_notice, :out_joinsetting,  
       :out_creatorid, :out_ownerid, :out_ownerusertype, :out_createtime,
       :out_me_ismanager, :out_me_lastrecvmsgsid, :out_me_msghintsetting, 
       :out_me_modifycardbymng, :out_me_card_name, :out_me_card_sex, :out_me_card_number, 
       :out_me_card_email, :out_me_card_remark, :out_me_jointime;

  suspend;
end##

/* MODIFYGROUP */

CREATE OR ALTER PROCEDURE MODIFYGROUP (
    in_groupid integer,
    in_groupname varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_notice varchar(255) character set unicode_fss,
    in_joinsetting smallint)
as
begin
  update GROUPS set GROUPNAME = :in_groupname, INTRODUCTION = :in_introduction, 
      NOTICE = :in_notice, JOINSETTING = :in_joinsetting
      where GROUPID = :in_groupid;
end##

/* REMOVEGROUP */

CREATE OR ALTER PROCEDURE REMOVEGROUP (
    in_groupid integer)
as
begin
  delete from GROUPSHAREFILES where GROUPID = :in_groupid;
  delete from GROUPMEMBERS where GROUPID = :in_groupid;
  delete from GROUPS where GROUPID = :in_groupid;
end##

/* CHANGEGROUPOWNER */

CREATE OR ALTER PROCEDURE CHANGEGROUPOWNER (
    in_groupid integer,
    in_newownerid integer)
returns (
    out_errorcode integer,
    out_oldowner_userid integer,
    out_oldowner_usertype smallint,
    out_newowner_usertype smallint)
as
  declare variable var_groupcount integer;
begin
  out_errorcode = 0;

  out_newowner_usertype = null;

  select USERTYPE from USERS where USERID = :in_newownerid into :out_newowner_usertype;

  if (out_newowner_usertype is null) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

  out_oldowner_userid = null;
  out_oldowner_usertype = null;

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

  if ((out_oldowner_userid is null) or (out_oldowner_usertype is null)) then
  begin
    out_errorcode = 2;
    suspend;
    EXIT;
  end

  if ((out_oldowner_usertype = 1) and (out_newowner_usertype = 0)) then
  begin
    out_errorcode = 3;
    suspend;
    EXIT;
  end

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

    if (var_groupcount >= 3) then
    begin
      out_errorcode = 4;
      suspend;
      EXIT;
    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;

  suspend;
end##

/* SETGROUPMANAGER */

CREATE OR ALTER PROCEDURE SETGROUPMANAGER (
    in_groupid integer,
    in_memberid integer,
    in_ismanager smallint)
returns (
    out_errorcode integer)
as
  declare variable var_usertype smallint;
  declare variable var_owneruserid integer;
  declare variable var_ownerusertype smallint;
  declare variable var_managercount integer;
begin
  out_errorcode = 0;

  if (not exists(select * from GROUPMEMBERS where MEMBERID = :in_memberid and GROUPID = :in_groupid)) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

  var_usertype = null;

  select USERTYPE from USERS where USERID = :in_memberid into :var_usertype;

  if (var_usertype is null) then
  begin
    out_errorcode = 2;
    suspend;
    EXIT;
  end

  var_ownerusertype = null;

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

  if ((var_owneruserid is null) or (var_ownerusertype is null)) then
  begin
    out_errorcode = 3;
    suspend;
    EXIT;
  end

  if ((var_ownerusertype = 1) and (var_usertype = 0)) then
  begin
    out_errorcode = 4;
    suspend;
    EXIT;
  end

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

      if (var_managercount >= 4) then
      begin
        out_errorcode = 5;
        suspend;
        EXIT;
      end

      update GROUPMEMBERS set ISMANAGER = 1 where MEMBERID = :in_memberid and GROUPID = :in_groupid;
  end
  else
  begin
    if (var_owneruserid = in_memberid) then
    begin
        out_errorcode = 6;
        suspend;
        EXIT;        
    end
      update GROUPMEMBERS set ISMANAGER = 0 where MEMBERID = :in_memberid and GROUPID = :in_groupid;
  end

  suspend;
end##

/* GETUSERGROUPS */

CREATE OR ALTER PROCEDURE GETUSERGROUPS (
    in_userid integer)
returns (
    out_groupid integer,
    out_groupname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp,

    out_me_ismanager smallint,
    out_me_lastrecvmsgsid bigint,
    out_me_msghintsetting smallint,
    out_me_modifycardbymng smallint,
    out_me_card_name varchar(20) character set unicode_fss,
    out_me_card_sex smallint,
    out_me_card_number varchar(20) character set unicode_fss,
    out_me_card_email varchar(50) character set unicode_fss,
    out_me_card_remark varchar(255) character set unicode_fss,
    out_me_jointime timestamp)
as
begin
  for select b.GROUPID, b.GROUPNAME, b.INTRODUCTION, b.NOTICE, b.JOINSETTING, 
       b.CREATORID, b.OWNERID, b.OWNERUSERTYPE, b.CREATETIME,
       a.ISMANAGER, a.LASTRECVMSGSID, a.MSGHINTSETTING, a.MODIFYCARDBYMNG, 
       a.CARD_NAME, a.CARD_SEX, a.CARD_NUMBER, a.CARD_EMAIL, a.CARD_REMARK, a.JOINTIME
       from GROUPMEMBERS as a inner join GROUPS as b on a.GROUPID = b.GROUPID
       where a.MEMBERID = :in_userid into 
       :out_groupid, :out_groupname, :out_introduction, :out_notice, :out_joinsetting,  
       :out_creatorid, :out_ownerid, :out_ownerusertype, :out_createtime,
       :out_me_ismanager, :out_me_lastrecvmsgsid, :out_me_msghintsetting, 
       :out_me_modifycardbymng, :out_me_card_name, :out_me_card_sex, :out_me_card_number, 
       :out_me_card_email, :out_me_card_remark, :out_me_jointime
    do
  begin
    suspend;
  end
end##

/* GETALLGROUPS */

CREATE OR ALTER PROCEDURE GETALLGROUPS
returns (
    out_groupid integer,
    out_groupname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp)
as
begin
  for select GROUPID, GROUPNAME, INTRODUCTION, NOTICE, JOINSETTING,
       CREATORID, OWNERID, OWNERUSERTYPE, CREATETIME
       from GROUPS where 1 = 1 into :out_groupid, :out_groupname,
       :out_introduction, :out_notice, :out_joinsetting, :out_creatorid, 
       :out_ownerid, :out_ownerusertype, :out_createtime
    do
  begin
    suspend;
  end
end##

/* ADDGROUPMEMBER */

CREATE OR ALTER PROCEDURE ADDGROUPMEMBER (
    in_groupid integer,
    in_userid integer)
returns (
    out_memberid integer,
    out_groupid integer,
    out_ismanager smallint,
    out_lastrecvmsgsid bigint,
    out_msghintsetting smallint,
    out_modifycardbymng smallint,
    out_card_name varchar(20) character set unicode_fss,
    out_card_sex smallint,
    out_card_number varchar(20) character set unicode_fss,
    out_card_email varchar(50) character set unicode_fss,
    out_card_remark varchar(255) character set unicode_fss,
    out_jointime timestamp,
    
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,
    
    out_loginstatus smallint)
as
begin
  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,
      '',
      '',
      '',
      CURRENT_TIMESTAMP
    );

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

  suspend;
end##

/* REMOVEGROUPMEMBER */

CREATE OR ALTER PROCEDURE REMOVEGROUPMEMBER (
    in_groupid integer,
    in_userid integer)
as
begin
  delete from GROUPSHAREFILES where MEMBERID = :in_userid and GROUPID = :in_groupid;
  delete from GROUPMEMBERS where MEMBERID = :in_userid and GROUPID = :in_groupid;
end##

/* MODIFYGROUPMEMBER */

CREATE OR ALTER PROCEDURE MODIFYGROUPMEMBER (
    in_groupid integer,
    in_memberid integer,
    in_msghintsetting smallint,
    in_modifycardbymng smallint,
    in_card_name varchar(20) character set unicode_fss,
    in_card_sex smallint,
    in_card_number varchar(20) character set unicode_fss,
    in_card_email varchar(50) character set unicode_fss,
    in_card_remark varchar(255) character set unicode_fss)
as
begin
  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;
end##

/* UPDATEGROUPLASTRECVMSGSID */

CREATE OR ALTER PROCEDURE UPDATEGROUPLASTRECVMSGSID (
    in_groupid integer,
    in_memberid integer)
as
  declare variable var_lastrecvmsgsid bigint;
begin
  select MAX(SID) from OFFGROUPMSGS 
    where GROUPID = :in_groupid into :var_lastrecvmsgsid;
  if (var_lastrecvmsgsid is null) then EXIT;
  update GROUPMEMBERS set LASTRECVMSGSID = :var_lastrecvmsgsid 
    where MEMBERID = :in_memberid and GROUPID = :in_groupid;
end##

/* GETGROUPMEMBERS */

CREATE OR ALTER PROCEDURE GETGROUPMEMBERS (
    in_groupid integer)
returns (
    out_memberid integer,
    out_groupid integer,
    out_ismanager smallint,
    out_lastrecvmsgsid bigint,
    out_msghintsetting smallint,
    out_modifycardbymng smallint,
    out_card_name varchar(20) character set unicode_fss,
    out_card_sex smallint,
    out_card_number varchar(20) character set unicode_fss,
    out_card_email varchar(50) character set unicode_fss,
    out_card_remark varchar(255) character set unicode_fss,
    out_jointime timestamp,

    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

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

/* ADDGROUPSHAREFILE */

CREATE OR ALTER PROCEDURE ADDGROUPSHAREFILE (
    in_sid varchar(50) character set ascii,
    in_memberid integer,
    in_groupid integer,
    in_filenameordir varchar(255) character set unicode_fss,
    in_isdir smallint,
    in_allfilecount integer,
    in_allfilesize bigint)
as
begin
  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,
    CURRENT_TIMESTAMP
    );
end##

/* REMOVEGROUPSHAREFILE */

CREATE OR ALTER PROCEDURE REMOVEGROUPSHAREFILE (
    in_groupid integer,
    in_sid varchar(50) character set ascii)
as
begin
  delete from GROUPSHAREFILES where SID = :in_sid and GROUPID = :in_groupid;
end##

/* GETGROUPSHAREFILES */

CREATE OR ALTER PROCEDURE GETGROUPSHAREFILES (
    in_groupid integer)
returns (
    out_sid varchar(50) character set ascii,
    out_memberid integer,
    out_member_nickname varchar(20) character set unicode_fss,
    out_member_actualname varchar(20) character set unicode_fss,
    out_groupid integer,
    out_filenameordir varchar(255) character set unicode_fss,
    out_isdir smallint,
    out_allfilecount integer,
    out_allfilesize bigint,
    out_createtime timestamp)
as
begin
  for select a.SID, a.MEMBERID, b.NICKNAME, b.ACTUALNAME, a.GROUPID, a.FILENAMEORDIR, 
    a.ISDIR, a.ALLFILECOUNT, a.ALLFILESIZE, a.CREATETIME 
    from GROUPSHAREFILES as a inner join USERS as b on a.MEMBERID = b.USERID 
    where a.GROUPID = :in_groupid into
    :out_sid, :out_memberid, :out_member_nickname, :out_member_actualname, :out_groupid, 
    :out_filenameordir, :out_isdir, :out_allfilecount, :out_allfilesize, :out_createtime 
    do
  begin
    suspend;
  end
end##

/* ADDJOINGROUPREQUEST */

CREATE OR ALTER PROCEDURE ADDJOINGROUPREQUEST (
    in_userid integer,
    in_groupid integer,
    in_content varchar(255) character set unicode_fss,
    in_sender integer)
as
begin
  if (exists(select * from JOINGROUPREQUESTS 
    where USERID = :in_userid and GROUPID = :in_groupid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINGROUPREQUESTS */

CREATE OR ALTER PROCEDURE GETJOINGROUPREQUESTS (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_groupid integer,
    out_content varchar(255) character set unicode_fss,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, GROUPID, CONTENT, SENDER, SENDTIME 
    from JOINGROUPREQUESTS where USERID = :in_userid into 
    :out_sid, :out_userid, :out_groupid, :out_content, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINGROUPREQUESTS where USERID = :in_userid;
end##

/* ADDJOINGROUPRESPONSE */

CREATE OR ALTER PROCEDURE ADDJOINGROUPRESPONSE (
    in_userid integer,
    in_groupid integer,
    in_content varchar(255) character set unicode_fss,
    in_agree smallint,
    in_sender integer)
as
begin
  if (exists(select * from JOINGROUPRESPONSES 
    where USERID = :in_userid and GROUPID = :in_groupid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINGROUPRESPONSES */

CREATE OR ALTER PROCEDURE GETJOINGROUPRESPONSES (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_groupid integer,
    out_content varchar(255) character set unicode_fss,
    out_agree smallint,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, GROUPID, CONTENT, AGREE, SENDER, SENDTIME 
    from JOINGROUPRESPONSES where USERID = :in_userid into 
    :out_sid, :out_userid, :out_groupid, :out_content, :out_agree, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINGROUPRESPONSES where USERID = :in_userid;
end##

/* ADDOFFGROUPMSG */

CREATE OR ALTER PROCEDURE ADDOFFGROUPMSG (
    in_groupid integer,
    in_msgid varchar(50) character set ascii,
    in_msglevel smallint,
    in_content varchar(8192) character set unicode_fss,
    in_font varchar(50) character set unicode_fss,
    in_sender_userid integer,
    in_sender_nickname varchar(20) character set unicode_fss,
    in_sender_actualname varchar(20) character set unicode_fss)
as
begin
  delete from OFFGROUPMSGS where GROUPID = :in_groupid and SENDTIME > addDay(CURRENT_TIMESTAMP, 7);

  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,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFGROUPMSG */

CREATE OR ALTER PROCEDURE REMOVEOFFGROUPMSG (
    in_sid bigint)
as
begin
  delete from OFFGROUPMSGS where SID = :in_sid;
end##

/* GETOFFGROUPMSGS */

CREATE OR ALTER PROCEDURE GETOFFGROUPMSGS (
    in_groupid integer,
    in_lastread_sid bigint)
returns (
    out_sid bigint,
    out_groupid integer,
    out_msgid varchar(50) character set ascii,
    out_msglevel smallint,
    out_content varchar(8192) character set unicode_fss,
    out_font varchar(50) character set unicode_fss,
    out_sender_userid integer,
    out_sender_nickname varchar(20) character set unicode_fss,
    out_sender_actualname varchar(20) character set unicode_fss,
    out_sendtime timestamp)
as
begin
  for select first 200 SID, GROUPID, MSGID, MSGLEVEL, CONTENT, FONT, SENDER_USERID, SENDER_NICKNAME, 
      SENDER_ACTUALNAME, SENDTIME from OFFGROUPMSGS
      where GROUPID = :in_groupid and SID > :in_lastread_sid order by SID ASC into 
      :out_sid, :out_groupid, :out_msgid, :out_msglevel, :out_content, :out_font, :out_sender_userid, 
      :out_sender_nickname, :out_sender_actualname, :out_sendtime
    do
  begin
    suspend;
  end
end##

/* ADDOFFGROUPSUPEROBJ */

CREATE OR ALTER PROCEDURE ADDOFFGROUPSUPEROBJ (
    in_groupid integer,
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer,
    in_superobjectcode varchar(1024) character set unicode_fss,
    in_localfilename varchar(1024) character set unicode_fss)
as
begin
  delete from OFFGROUPSUPEROBJS where GROUPID = :in_groupid and CREATETIME > addDay(CURRENT_TIMESTAMP, 7);

  insert into OFFGROUPSUPEROBJS(
    GROUPID,
    MSGID,
    FROMUSERID,
    SUPEROBJCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    :in_groupid,
    :in_msgid,
    :in_fromuserid,
    :in_superobjectcode,
    :in_localfilename,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFGROUPSUPEROBJ */

CREATE OR ALTER PROCEDURE REMOVEOFFGROUPSUPEROBJ (
    in_sid bigint)
as
begin
  delete from OFFGROUPSUPEROBJS where SID = :in_sid;
end##

/* GETOFFGROUPSUPEROBJS */

CREATE OR ALTER PROCEDURE GETOFFGROUPSUPEROBJS (
    in_groupid integer,
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer)
returns (
    out_sid bigint,
    out_groupid integer,
    out_msgid varchar(50) character set unicode_fss,
    out_fromuserid integer,
    out_superobjectcode varchar(1024) character set unicode_fss,
    out_localfilename varchar(1024) character set unicode_fss,
    out_createtime timestamp)
as
begin
  for select SID, GROUPID, MSGID, FROMUSERID, SUPEROBJCODE, LOCALFILENAME, CREATETIME
    from OFFGROUPSUPEROBJS where GROUPID = :in_groupid and MSGID = :in_msgid and FROMUSERID = :in_fromuserid into 
    :out_sid, :out_groupid, :out_msgid, :out_fromuserid, :out_superobjectcode, :out_localfilename, :out_createtime do
  begin
    suspend;
  end
end##

/* ADDROOM */

CREATE OR ALTER PROCEDURE ADDROOM (
    in_roomname varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_notice varchar(255) character set unicode_fss,
    in_joinsetting smallint,
    in_creatorid integer)
returns (
    out_errorcode integer,

    out_roomid integer,
    out_roomname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp,
    
    out_me_ismanager smallint,
    out_me_lastrecvmsgsid bigint,
    out_me_msghintsetting smallint,
    out_me_modifycardbymng smallint,
    out_me_card_name varchar(20) character set unicode_fss,
    out_me_card_sex smallint,
    out_me_card_number varchar(20) character set unicode_fss,
    out_me_card_email varchar(50) character set unicode_fss,
    out_me_card_remark varchar(255) character set unicode_fss,
    out_me_jointime timestamp)
as
  declare variable var_usertype smallint;
  declare variable var_newroomid integer;
  declare variable var_roomcount integer;
begin
  out_errorcode = 0;

  var_usertype = null;

  select USERTYPE from USERS where USERID = :in_creatorid into :var_usertype;

  if (var_usertype is null) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

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

    if (var_roomcount >= 3) then
    begin
      out_errorcode = 2;
      suspend;
      EXIT;
    end
  end

  select max(ROOMID) from ROOMS into :var_newroomid;
  if (var_newroomid is null) then var_newroomid = 10000 - 1;
  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,
      CURRENT_TIMESTAMP
    );

  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,
      0,
      1,
      '',
      0,
      '',
      '',
      '',
      CURRENT_TIMESTAMP
    );

  select first 1 b.ROOMID, b.ROOMNAME, b.INTRODUCTION, b.NOTICE, b.JOINSETTING, 
       b.CREATORID, b.OWNERID, b.OWNERUSERTYPE, b.CREATETIME,
       a.ISMANAGER, a.LASTRECVMSGSID, a.MSGHINTSETTING, a.MODIFYCARDBYMNG, 
       a.CARD_NAME, a.CARD_SEX, a.CARD_NUMBER, a.CARD_EMAIL, a.CARD_REMARK, a.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 into 
       :out_roomid, :out_roomname, :out_introduction, :out_notice, :out_joinsetting,  
       :out_creatorid, :out_ownerid, :out_ownerusertype, :out_createtime,
       :out_me_ismanager, :out_me_lastrecvmsgsid, :out_me_msghintsetting, 
       :out_me_modifycardbymng, :out_me_card_name, :out_me_card_sex, :out_me_card_number, 
       :out_me_card_email, :out_me_card_remark, :out_me_jointime;

  suspend;
end##

/* MODIFYROOM */

CREATE OR ALTER PROCEDURE MODIFYROOM (
    in_roomid integer,
    in_roomname varchar(20) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_notice varchar(255) character set unicode_fss,
    in_joinsetting smallint)
as
begin
  update ROOMS set ROOMNAME = :in_roomname, INTRODUCTION = :in_introduction, 
      NOTICE = :in_notice, JOINSETTING = :in_joinsetting
      where ROOMID = :in_roomid;
end##

/* REMOVEROOM */

CREATE OR ALTER PROCEDURE REMOVEROOM (
    in_roomid integer)
as
begin
  delete from ROOMSHAREFILES where ROOMID = :in_roomid;
  delete from ROOMMEMBERS where ROOMID = :in_roomid;
  delete from ROOMS where ROOMID = :in_roomid;
end##

/* CHANGEROOMOWNER */

CREATE OR ALTER PROCEDURE CHANGEROOMOWNER (
    in_roomid integer,
    in_newownerid integer)
returns (
    out_errorcode integer,
    out_oldowner_userid integer,
    out_oldowner_usertype smallint,
    out_newowner_usertype smallint)
as
  declare variable var_roomcount integer;
begin
  out_errorcode = 0;

  out_newowner_usertype = null;

  select USERTYPE from USERS where USERID = :in_newownerid into :out_newowner_usertype;

  if (out_newowner_usertype is null) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

  out_oldowner_userid = null;
  out_oldowner_usertype = null;

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

  if ((out_oldowner_userid is null) or (out_oldowner_usertype is null)) then
  begin
    out_errorcode = 2;
    suspend;
    EXIT;
  end

  if ((out_oldowner_usertype = 1) and (out_newowner_usertype = 0)) then
  begin
    out_errorcode = 3;
    suspend;
    EXIT;
  end

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

    if (var_roomcount >= 3) then
    begin
      out_errorcode = 4;
      suspend;
      EXIT;
    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;

  suspend;
end##

/* SETROOMMANAGER */

CREATE OR ALTER PROCEDURE SETROOMMANAGER (
    in_roomid integer,
    in_memberid integer,
    in_ismanager smallint)
returns (
    out_errorcode integer)
as
  declare variable var_usertype smallint;
  declare variable var_owneruserid integer;
  declare variable var_ownerusertype smallint;
  declare variable var_managercount integer;
begin
  out_errorcode = 0;

  if (not exists(select * from ROOMMEMBERS where MEMBERID = :in_memberid and ROOMID = :in_roomid)) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

  var_usertype = null;

  select USERTYPE from USERS where USERID = :in_memberid into :var_usertype;

  if (var_usertype is null) then
  begin
    out_errorcode = 2;
    suspend;
    EXIT;
  end

  var_ownerusertype = null;

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

  if ((var_owneruserid is null) or (var_ownerusertype is null)) then
  begin
    out_errorcode = 3;
    suspend;
    EXIT;
  end

  if ((var_ownerusertype = 1) and (var_usertype = 0)) then
  begin
    out_errorcode = 4;
    suspend;
    EXIT;
  end

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

      if (var_managercount >= 4) then
      begin
        out_errorcode = 5;
        suspend;
        EXIT;
      end

      update ROOMMEMBERS set ISMANAGER = 1 where MEMBERID = :in_memberid and ROOMID = :in_roomid;
  end
  else
  begin
    if (var_owneruserid = in_memberid) then
    begin
        out_errorcode = 6;
        suspend;
        EXIT;        
    end
      update ROOMMEMBERS set ISMANAGER = 0 where MEMBERID = :in_memberid and ROOMID = :in_roomid;
  end

  suspend;
end##

/* GETUSERROOMS */

CREATE OR ALTER PROCEDURE GETUSERROOMS (
    in_userid integer)
returns (
    out_roomid integer,
    out_roomname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp,

    out_me_ismanager smallint,
    out_me_lastrecvmsgsid bigint,
    out_me_msghintsetting smallint,
    out_me_modifycardbymng smallint,
    out_me_card_name varchar(20) character set unicode_fss,
    out_me_card_sex smallint,
    out_me_card_number varchar(20) character set unicode_fss,
    out_me_card_email varchar(50) character set unicode_fss,
    out_me_card_remark varchar(255) character set unicode_fss,
    out_me_jointime timestamp)
as
begin
  for select b.ROOMID, b.ROOMNAME, b.INTRODUCTION, b.NOTICE, b.JOINSETTING, 
       b.CREATORID, b.OWNERID, b.OWNERUSERTYPE, b.CREATETIME,
       a.ISMANAGER, a.LASTRECVMSGSID, a.MSGHINTSETTING, a.MODIFYCARDBYMNG, 
       a.CARD_NAME, a.CARD_SEX, a.CARD_NUMBER, a.CARD_EMAIL, a.CARD_REMARK, a.JOINTIME
       from ROOMMEMBERS as a inner join ROOMS as b on a.ROOMID = b.ROOMID
       where a.MEMBERID = :in_userid into 
       :out_roomid, :out_roomname, :out_introduction, :out_notice, :out_joinsetting,  
       :out_creatorid, :out_ownerid, :out_ownerusertype, :out_createtime,
       :out_me_ismanager, :out_me_lastrecvmsgsid, :out_me_msghintsetting, 
       :out_me_modifycardbymng, :out_me_card_name, :out_me_card_sex, :out_me_card_number, 
       :out_me_card_email, :out_me_card_remark, :out_me_jointime
    do
  begin
    suspend;
  end
end##

/* GETALLROOMS */

CREATE OR ALTER PROCEDURE GETALLROOMS
returns (
    out_roomid integer,
    out_roomname varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_notice varchar(255) character set unicode_fss,
    out_joinsetting smallint,
    out_creatorid integer,
    out_ownerid integer,
    out_ownerusertype smallint,
    out_createtime timestamp)
as
begin
  for select ROOMID, ROOMNAME, INTRODUCTION, NOTICE, JOINSETTING,
       CREATORID, OWNERID, OWNERUSERTYPE, CREATETIME
       from ROOMS where 1 = 1 into :out_roomid, :out_roomname,
       :out_introduction, :out_notice, :out_joinsetting, :out_creatorid, 
       :out_ownerid, :out_ownerusertype, :out_createtime
    do
  begin
    suspend;
  end
end##

/* ADDROOMMEMBER */

CREATE OR ALTER PROCEDURE ADDROOMMEMBER (
    in_roomid integer,
    in_userid integer)
returns (
    out_memberid integer,
    out_roomid integer,
    out_ismanager smallint,
    out_lastrecvmsgsid bigint,
    out_msghintsetting smallint,
    out_modifycardbymng smallint,
    out_card_name varchar(20) character set unicode_fss,
    out_card_sex smallint,
    out_card_number varchar(20) character set unicode_fss,
    out_card_email varchar(50) character set unicode_fss,
    out_card_remark varchar(255) character set unicode_fss,
    out_jointime timestamp,
    
    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

    out_facefile varchar(255) character set unicode_fss,
    out_photofile varchar(255) character set unicode_fss,
    
    out_loginstatus smallint)
as
begin
  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,
      '',
      '',
      '',
      CURRENT_TIMESTAMP
    );

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

  suspend;
end##

/* REMOVEROOMMEMBER */

CREATE OR ALTER PROCEDURE REMOVEROOMMEMBER (
    in_roomid integer,
    in_userid integer)
as
begin
  delete from ROOMSHAREFILES where MEMBERID = :in_userid and ROOMID = :in_roomid;
  delete from ROOMMEMBERS where MEMBERID = :in_userid and ROOMID = :in_roomid;
end##

/* MODIFYROOMMEMBER */

CREATE OR ALTER PROCEDURE MODIFYROOMMEMBER (
    in_roomid integer,
    in_memberid integer,
    in_msghintsetting smallint,
    in_modifycardbymng smallint,
    in_card_name varchar(20) character set unicode_fss,
    in_card_sex smallint,
    in_card_number varchar(20) character set unicode_fss,
    in_card_email varchar(50) character set unicode_fss,
    in_card_remark varchar(255) character set unicode_fss)
as
begin
  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;
end##

/* UPDATEROOMLASTRECVMSGSID */

CREATE OR ALTER PROCEDURE UPDATEROOMLASTRECVMSGSID (
    in_roomid integer,
    in_memberid integer)
as
  declare variable var_lastrecvmsgsid bigint;
begin
  select MAX(SID) from OFFROOMMSGS 
    where ROOMID = :in_roomid into :var_lastrecvmsgsid;
  if (var_lastrecvmsgsid is null) then EXIT;
  update ROOMMEMBERS set LASTRECVMSGSID = :var_lastrecvmsgsid 
    where MEMBERID = :in_memberid and ROOMID = :in_roomid;
end##

/* GETROOMMEMBERS */

CREATE OR ALTER PROCEDURE GETROOMMEMBERS (
    in_roomid integer)
returns (
    out_memberid integer,
    out_roomid integer,
    out_ismanager smallint,
    out_lastrecvmsgsid bigint,
    out_msghintsetting smallint,
    out_modifycardbymng smallint,
    out_card_name varchar(20) character set unicode_fss,
    out_card_sex smallint,
    out_card_number varchar(20) character set unicode_fss,
    out_card_email varchar(50) character set unicode_fss,
    out_card_remark varchar(255) character set unicode_fss,
    out_jointime timestamp,

    out_username varchar(50) character set unicode_fss,
    out_usertype smallint,

    out_nickname varchar(20) character set unicode_fss,
    out_signature varchar(255) character set unicode_fss,
    out_actualname varchar(20) character set unicode_fss,
    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 varchar(50) character set unicode_fss,
    out_telephone varchar(20) character set unicode_fss,
    out_telephone_short varchar(20) character set unicode_fss,
    out_telephone2 varchar(20) character set unicode_fss,
    out_telephone2_short varchar(20) character set unicode_fss,
    out_mobile varchar(20) character set unicode_fss,
    out_mobile_short varchar(20) character set unicode_fss,
    out_mobile2 varchar(20) character set unicode_fss,
    out_mobile2_short varchar(20) character set unicode_fss,
    out_fax varchar(20) character set unicode_fss,
    out_qq varchar(50) character set unicode_fss,
    out_weixin varchar(50) character set unicode_fss,
    out_weibo varchar(50) character set unicode_fss,
    out_email varchar(50) character set unicode_fss,
    out_roomname varchar(50) character set unicode_fss,
    out_roomnumber varchar(50) character set unicode_fss,
    out_idnumber varchar(50) character set unicode_fss,
    out_carnumber varchar(50) character set unicode_fss,
    out_homepage varchar(80) character set unicode_fss,
    out_departid integer,
    out_departname varchar(50) character set unicode_fss,
    out_jobtitle varchar(50) character set unicode_fss,
    out_jobnumber varchar(20) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,

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

/* ADDROOMSHAREFILE */

CREATE OR ALTER PROCEDURE ADDROOMSHAREFILE (
    in_sid varchar(50) character set ascii,
    in_memberid integer,
    in_roomid integer,
    in_filenameordir varchar(255) character set unicode_fss,
    in_isdir smallint,
    in_allfilecount integer,
    in_allfilesize bigint)
as
begin
  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,
    CURRENT_TIMESTAMP
    );
end##

/* REMOVEROOMSHAREFILE */

CREATE OR ALTER PROCEDURE REMOVEROOMSHAREFILE (
    in_roomid integer,
    in_sid varchar(50) character set ascii)
as
begin
  delete from ROOMSHAREFILES where SID = :in_sid and ROOMID = :in_roomid;
end##

/* GETROOMSHAREFILES */

CREATE OR ALTER PROCEDURE GETROOMSHAREFILES (
    in_roomid integer)
returns (
    out_sid varchar(50) character set ascii,
    out_memberid integer,
    out_member_nickname varchar(20) character set unicode_fss,
    out_member_actualname varchar(20) character set unicode_fss,
    out_roomid integer,
    out_filenameordir varchar(255) character set unicode_fss,
    out_isdir smallint,
    out_allfilecount integer,
    out_allfilesize bigint,
    out_createtime timestamp)
as
begin
  for select a.SID, a.MEMBERID, b.NICKNAME, b.ACTUALNAME, a.ROOMID, a.FILENAMEORDIR, 
    a.ISDIR, a.ALLFILECOUNT, a.ALLFILESIZE, a.CREATETIME 
    from ROOMSHAREFILES as a inner join USERS as b on a.MEMBERID = b.USERID 
    where a.ROOMID = :in_roomid into
    :out_sid, :out_memberid, :out_member_nickname, :out_member_actualname, :out_roomid, 
    :out_filenameordir, :out_isdir, :out_allfilecount, :out_allfilesize, :out_createtime 
    do
  begin
    suspend;
  end
end##

/* ADDJOINROOMREQUEST */

CREATE OR ALTER PROCEDURE ADDJOINROOMREQUEST (
    in_userid integer,
    in_roomid integer,
    in_content varchar(255) character set unicode_fss,
    in_sender integer)
as
begin
  if (exists(select * from JOINROOMREQUESTS 
    where USERID = :in_userid and ROOMID = :in_roomid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINROOMREQUESTS */

CREATE OR ALTER PROCEDURE GETJOINROOMREQUESTS (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_roomid integer,
    out_content varchar(255) character set unicode_fss,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, ROOMID, CONTENT, SENDER, SENDTIME 
    from JOINROOMREQUESTS where USERID = :in_userid into 
    :out_sid, :out_userid, :out_roomid, :out_content, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINROOMREQUESTS where USERID = :in_userid;
end##

/* ADDJOINROOMRESPONSE */

CREATE OR ALTER PROCEDURE ADDJOINROOMRESPONSE (
    in_userid integer,
    in_roomid integer,
    in_content varchar(255) character set unicode_fss,
    in_agree smallint,
    in_sender integer)
as
begin
  if (exists(select * from JOINROOMRESPONSES 
    where USERID = :in_userid and ROOMID = :in_roomid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINROOMRESPONSES */

CREATE OR ALTER PROCEDURE GETJOINROOMRESPONSES (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_roomid integer,
    out_content varchar(255) character set unicode_fss,
    out_agree smallint,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, ROOMID, CONTENT, AGREE, SENDER, SENDTIME 
    from JOINROOMRESPONSES where USERID = :in_userid into 
    :out_sid, :out_userid, :out_roomid, :out_content, :out_agree, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINROOMRESPONSES where USERID = :in_userid;
end##

/* ADDOFFROOMMSG */

CREATE OR ALTER PROCEDURE ADDOFFROOMMSG (
    in_roomid integer,
    in_msgid varchar(50) character set ascii,
    in_msglevel smallint,
    in_content varchar(8192) character set unicode_fss,
    in_font varchar(50) character set unicode_fss,
    in_sender_userid integer,
    in_sender_nickname varchar(20) character set unicode_fss,
    in_sender_actualname varchar(20) character set unicode_fss)
as
begin
  delete from OFFROOMMSGS where ROOMID = :in_roomid and SENDTIME > addDay(CURRENT_TIMESTAMP, 7);

  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,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFROOMMSG */

CREATE OR ALTER PROCEDURE REMOVEOFFROOMMSG (
    in_sid bigint)
as
begin
  delete from OFFROOMMSGS where SID = :in_sid;
end##

/* GETOFFROOMMSGS */

CREATE OR ALTER PROCEDURE GETOFFROOMMSGS (
    in_roomid integer,
    in_lastread_sid bigint)
returns (
    out_sid bigint,
    out_roomid integer,
    out_msgid varchar(50) character set ascii,
    out_msglevel smallint,
    out_content varchar(8192) character set unicode_fss,
    out_font varchar(50) character set unicode_fss,
    out_sender_userid integer,
    out_sender_nickname varchar(20) character set unicode_fss,
    out_sender_actualname varchar(20) character set unicode_fss,
    out_sendtime timestamp)
as
begin
  for select first 200 SID, ROOMID, MSGID, MSGLEVEL, CONTENT, FONT, SENDER_USERID, SENDER_NICKNAME, 
      SENDER_ACTUALNAME, SENDTIME from OFFROOMMSGS
      where ROOMID = :in_roomid and SID > :in_lastread_sid order by SID ASC into 
      :out_sid, :out_roomid, :out_msgid, :out_msglevel, :out_content, :out_font, :out_sender_userid, 
      :out_sender_nickname, :out_sender_actualname, :out_sendtime
    do
  begin
    suspend;
  end
end##

/* ADDOFFROOMSUPEROBJ */

CREATE OR ALTER PROCEDURE ADDOFFROOMSUPEROBJ (
    in_roomid integer,
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer,
    in_superobjectcode varchar(1024) character set unicode_fss,
    in_localfilename varchar(1024) character set unicode_fss)
as
begin
  delete from OFFROOMSUPEROBJS where ROOMID = :in_roomid and CREATETIME > addDay(CURRENT_TIMESTAMP, 7);

  insert into OFFROOMSUPEROBJS(
    ROOMID,
    MSGID,
    FROMUSERID,
    SUPEROBJCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    :in_roomid,
    :in_msgid,
    :in_fromuserid,
    :in_superobjectcode,
    :in_localfilename,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFROOMSUPEROBJ */

CREATE OR ALTER PROCEDURE REMOVEOFFROOMSUPEROBJ (
    in_sid bigint)
as
begin
  delete from OFFROOMSUPEROBJS where SID = :in_sid;
end##

/* GETOFFROOMSUPEROBJS */

CREATE OR ALTER PROCEDURE GETOFFROOMSUPEROBJS (
    in_roomid integer,
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer)
returns (
    out_sid bigint,
    out_roomid integer,
    out_msgid varchar(50) character set unicode_fss,
    out_fromuserid integer,
    out_superobjectcode varchar(1024) character set unicode_fss,
    out_localfilename varchar(1024) character set unicode_fss,
    out_createtime timestamp)
as
begin
  for select SID, ROOMID, MSGID, FROMUSERID, SUPEROBJCODE, LOCALFILENAME, CREATETIME
    from OFFROOMSUPEROBJS where ROOMID = :in_roomid and MSGID = :in_msgid and FROMUSERID = :in_fromuserid into 
    :out_sid, :out_roomid, :out_msgid, :out_fromuserid, :out_superobjectcode, :out_localfilename, :out_createtime do
  begin
    suspend;
  end
end##

/* GETWEBVISITORID */

CREATE OR ALTER PROCEDURE GETWEBVISITORID (
    in_visitorcode varchar(50) character set unicode_fss)
returns (
    out_visitorid integer)
as
begin
  out_visitorid = 0;

  if (in_visitorcode != '') then
  begin
    select first 1 VISITORID from WEBVISITORS where VISITORCODE = :in_visitorcode into :out_visitorid;
  end

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

  suspend;
end##

/* ADDWEBVISITOR */

CREATE OR ALTER PROCEDURE ADDWEBVISITOR (
    in_visitorid integer,
    in_visitorcode varchar(50) character set unicode_fss,
    in_userid integer,
    in_srcurl    varchar(1024) character set unicode_fss,
    in_clientipaddr varchar(50) character set unicode_fss,
    in_clientlocation varchar(50) character set unicode_fss,
    in_clientos varchar(50) character set unicode_fss,
    in_webbrowser varchar(50) character set unicode_fss,
    in_createtime timestamp)
returns (
    out_visitorid integer,
    out_visitorcode varchar(50) character set unicode_fss,
    out_userid integer,
    out_srcurl varchar(1024) character set unicode_fss,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_clientos varchar(50) character set unicode_fss,
    out_webbrowser varchar(50) character set unicode_fss,
    out_remarkname varchar(20) character set unicode_fss,
    out_remarktext varchar(1024) character set unicode_fss,
    out_flag smallint,
    out_isactive smallint,
    out_logintimes integer,
    out_lastlogintime timestamp,
    out_sendmsgs integer,
    out_recvmsgs integer,
    out_leavemsgs integer,
    out_createtime timestamp)
as
begin
  if (exists(select * from WEBVISITORS where VISITORID = :in_visitorid and USERID = :in_userid)) then
  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

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

  suspend;
end##

/* REMOVEWEBVISITOR */

CREATE OR ALTER PROCEDURE REMOVEWEBVISITOR (
    in_visitorid integer,
    in_userid integer)
as
begin
  delete from WEBVISITORS where (VISITORID = :in_visitorid) and (USERID = :in_userid) and (ISACTIVE = 0);
end##

/* CHANGEWEBVISITORREMARKNAME */

CREATE OR ALTER PROCEDURE CHANGEWEBVISITORREMARKNAME (
    in_visitorid integer,
    in_remarkname varchar(20) character set unicode_fss)
as
begin
  update WEBVISITORS set REMARKNAME = :in_remarkname where VISITORID = :in_visitorid;
end##

CREATE OR ALTER PROCEDURE CHANGEWEBVISITORREMARKTEXT (
    in_visitorid integer,
    in_remarktext varchar(1024) character set unicode_fss)
as
begin
  update WEBVISITORS set REMARKTEXT = :in_remarktext where VISITORID = :in_visitorid;
end##

/* WEBVISITORINACTIVE */

CREATE OR ALTER PROCEDURE WEBVISITORINACTIVE (
    in_visitorid integer)
as
begin
  update WEBVISITORS set ISACTIVE = 0 where VISITORID = :in_visitorid;
end##

/* WEBVISITORINCSENDMSGS */

CREATE OR ALTER PROCEDURE WEBVISITORINCSENDMSGS (
    in_visitorid integer)
as
begin
  update WEBVISITORS set SENDMSGS = SENDMSGS + 1 where VISITORID = :in_visitorid;
end##

/* WEBVISITORINCRECVMSGS */

CREATE OR ALTER PROCEDURE WEBVISITORINCRECVMSGS (
    in_visitorid integer)
as
begin
  update WEBVISITORS set RECVMSGS = RECVMSGS + 1 where VISITORID = :in_visitorid;
end##

/* WEBVISITORINCLEAVEMSGS */

CREATE OR ALTER PROCEDURE WEBVISITORINCLEAVEMSGS (
    in_visitorid integer)
as
begin
  update WEBVISITORS set LEAVEMSGS = LEAVEMSGS + 1 where VISITORID = :in_visitorid;
end##

/* GETINACTIVEWEBVISITORS */

CREATE OR ALTER PROCEDURE GETINACTIVEWEBVISITORS (
    in_userid integer)
returns (
    out_visitorid integer,
    out_visitorcode varchar(50) character set unicode_fss,
    out_userid integer,
    out_srcurl varchar(1024) character set unicode_fss,
    out_clientipaddr varchar(50) character set unicode_fss,
    out_clientlocation varchar(50) character set unicode_fss,
    out_clientos varchar(50) character set unicode_fss,
    out_webbrowser varchar(50) character set unicode_fss,
    out_remarkname varchar(20) character set unicode_fss,
    out_remarktext varchar(1024) character set unicode_fss,
    out_flag smallint,
    out_isactive smallint,
    out_logintimes integer,
    out_lastlogintime timestamp,
    out_sendmsgs integer,
    out_recvmsgs integer,
    out_leavemsgs integer,
    out_createtime timestamp)
as
begin
  for select VISITORID, VISITORCODE, USERID, SRCURL, CLIENTIPADDR, CLIENTLOCATION, CLIENTOS, 
        WEBBROWSER, REMARKNAME, REMARKTEXT, FLAG, ISACTIVE, LOGINTIMES, LASTLOGINTIME, SENDMSGS, RECVMSGS, LEAVEMSGS, CREATETIME 
    from WEBVISITORS where (USERID = :in_userid) and (ISACTIVE = 0) order by VISITORID DESC rows 1 to 100 into 
    :out_visitorid, :out_visitorcode, :out_userid, :out_srcurl, :out_clientipaddr, 
    :out_clientlocation, :out_clientos, :out_webbrowser, :out_remarkname, :out_remarktext, :out_flag, out_isactive,
    :out_logintimes, :out_lastlogintime, :out_sendmsgs, :out_recvmsgs, :out_leavemsgs, :out_createtime do
  begin
    suspend;
  end
end##

/* ADDWEBVISITORMESSAGE */

CREATE OR ALTER PROCEDURE ADDWEBVISITORMESSAGE (
    in_visitorid integer,
    in_userid integer,
    in_senderisvisitor smallint,
    in_content varchar(1024) character set unicode_fss,
    in_createtime timestamp)
as
begin
  insert into WEBVISITORMESSAGES(VISITORID, USERID, SENDERISVISITOR, CONTENT, CREATETIME) 
      values(:in_visitorid, :in_userid, :in_senderisvisitor, :in_content, :in_createtime);
end##

/* GETRECENTWEBVISITORMESSAGES */

CREATE OR ALTER PROCEDURE GETRECENTWEBVISITORMESSAGES (
    in_visitorid integer,
    in_userid integer,
    in_recordcount integer)
returns (
    out_visitorid integer,
    out_userid integer,
    out_senderisvisitor smallint,
    out_content varchar(1024) character set unicode_fss,
    out_createtime timestamp)
as
begin
  for select VISITORID, USERID, SENDERISVISITOR, CONTENT, CREATETIME 
       from WEBVISITORMESSAGES where (VISITORID = :in_visitorid) and (USERID = :in_userid) 
       order by SID DESC rows 1 to :in_recordcount into 
       :out_visitorid, :out_userid, :out_senderisvisitor, :out_content, :out_createtime do
  begin
    suspend;
  end
end##

/* GETWEBVISITORMESSAGES */

CREATE OR ALTER PROCEDURE GETWEBVISITORMESSAGES (
    in_visitorid integer,
    in_userid integer,
    in_pagesize integer,
    in_pageindex integer)
returns (
    out_maxpageindex integer,
    out_visitorid integer,
    out_userid integer,
    out_senderisvisitor smallint,
    out_content varchar(1024) character set unicode_fss,
    out_createtime timestamp)
as
  declare variable var_messagecount integer;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
begin
  if (in_pagesize <= 0) then in_pagesize = 40;

  select count(*) from WEBVISITORMESSAGES where (VISITORID = :in_visitorid) and (USERID = :in_userid) into :var_messagecount;
  out_maxpageindex = var_messagecount / :in_pagesize;
  if (Mod(var_messagecount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

  if (in_pageindex <= 0) then in_pageindex = out_maxpageindex;

  var_where_sql = '(VISITORID = ' || cast(:in_visitorid as varchar(10)) || ') and (USERID = ' ||
        cast(:in_userid as varchar(10)) || ') order by SID ASC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select VISITORID, USERID, SENDERISVISITOR, CONTENT, CREATETIME ' ||
       'from WEBVISITORMESSAGES where ' || var_where_sql || ' ' into 
       :out_visitorid, :out_userid, :out_senderisvisitor, :out_content, :out_createtime do
  begin
    suspend;
  end
end##

/* ADDWEBAPP */

CREATE OR ALTER PROCEDURE ADDWEBAPP (
    in_foruserid integer,
    in_forusertype smallint,
    in_appname varchar(50) character set unicode_fss,
    in_category varchar(50) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_appimage varchar(255) character set unicode_fss,
    in_appurl varchar(1024) character set unicode_fss,
    in_method smallint,
    in_postdata varchar(1024) character set unicode_fss,
    in_popup integer,
    in_clientwebbrowser smallint,
    in_webbrowserwidth integer,
    in_webbrowserheight integer,
    in_shortcut smallint)
returns (
    out_appid integer,
    out_foruserid integer,
    out_forusertype smallint,
    out_appname varchar(50) character set unicode_fss,
    out_category varchar(50) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_appimage varchar(255) character set unicode_fss,
    out_appurl varchar(1024) character set unicode_fss,
    out_method smallint,
    out_postdata varchar(1024) character set unicode_fss,
    out_popup integer,
    out_clientwebbrowser smallint,
    out_webbrowserwidth integer,
    out_webbrowserheight integer,
    out_shortcut smallint,
    out_ordervalue integer,
    out_createtime timestamp)
as
   declare variable var_newappid integer;
   declare variable var_ordervalue integer;
begin
  select max(APPID) from WEBAPPS into :var_newappid;
  if (var_newappid is null) then var_newappid = 0;
  var_newappid = var_newappid + 1;

  select max(ORDERVALUE) from WEBAPPS into :var_ordervalue;
  if (var_ordervalue is null) then var_ordervalue = 0;
  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,
    CURRENT_TIMESTAMP
    );

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

  suspend;
end##

/* REMOVEWEBAPP */

CREATE OR ALTER PROCEDURE REMOVEWEBAPP (
    in_appid integer)
returns (
    out_appimage varchar(255) character set unicode_fss)
as
begin
  select APPIMAGE from WEBAPPS where APPID = :in_appid into :out_appimage;
  delete from WEBAPPS where APPID = :in_appid;
  suspend;
end##

/* MODIFYWEBAPP */

CREATE OR ALTER PROCEDURE MODIFYWEBAPP (
    in_appid integer,
    in_appname varchar(50) character set unicode_fss,
    in_category varchar(50) character set unicode_fss,
    in_introduction varchar(255) character set unicode_fss,
    in_appurl varchar(1024) character set unicode_fss,
    in_method smallint,
    in_postdata varchar(1024) character set unicode_fss,
    in_popup integer,
    in_clientwebbrowser smallint,
    in_webbrowserwidth integer,
    in_webbrowserheight integer,
    in_shortcut smallint)
as
begin
  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;
end##

/* CHANGEWEBAPPIMAGE */

CREATE OR ALTER PROCEDURE CHANGEWEBAPPIMAGE (
    in_appid integer,
    in_appimage varchar(255) character set unicode_fss)
returns (
    out_appimage varchar(255) character set unicode_fss)
as
begin
  select APPIMAGE from WEBAPPS where APPID = :in_appid into :out_appimage;
  update WEBAPPS set APPIMAGE = :in_appimage where APPID = :in_appid;
  suspend;
end##

/* CHANGEWEBAPPORDERVALUE */

CREATE OR ALTER PROCEDURE CHANGEWEBAPPORDERVALUE (
    in_appid integer,
    in_ordervalue integer)
as
begin
  update WEBAPPS set ORDERVALUE = :in_ordervalue where APPID = :in_appid;
end##

/* GETWEBAPP */

CREATE OR ALTER PROCEDURE GETWEBAPP (
    in_appid integer)
returns (
    out_appid integer,
    out_foruserid integer,
    out_forusertype smallint,
    out_appname varchar(50) character set unicode_fss,
    out_category varchar(50) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_appimage varchar(255) character set unicode_fss,
    out_appurl varchar(1024) character set unicode_fss,
    out_method smallint,
    out_postdata varchar(1024) character set unicode_fss,
    out_popup integer,
    out_clientwebbrowser smallint,
    out_webbrowserwidth integer,
    out_webbrowserheight integer,
    out_shortcut smallint,
    out_ordervalue integer,
    out_createtime timestamp)
as
begin
    for select APPID, FORUSERID, FORUSERTYPE, APPNAME, CATEGORY, INTRODUCTION, APPIMAGE,  APPURL, 
       METHOD, POSTDATA, POPUP, CLIENTWEBBROWSER, WEBBROWSERWIDTH, WEBBROWSERHEIGHT, SHORTCUT, ORDERVALUE, CREATETIME
       from WEBAPPS where APPID = :in_appid into :out_appid, :out_foruserid, :out_forusertype, :out_appname, 
       :out_category, :out_introduction, :out_appimage, :out_appurl, :out_method, :out_postdata, :out_popup, :out_clientwebbrowser, 
       :out_webbrowserwidth, :out_webbrowserheight, :out_shortcut, :out_ordervalue, :out_createtime do
    begin
        suspend;
    end
end##

/* GETUSERWEBAPPS */

CREATE OR ALTER PROCEDURE GETUSERWEBAPPS (
    in_userid integer,
    in_usertype smallint)
returns (
    out_appid integer,
    out_foruserid integer,
    out_forusertype smallint,
    out_appname varchar(50) character set unicode_fss,
    out_category varchar(50) character set unicode_fss,
    out_introduction varchar(255) character set unicode_fss,
    out_appimage varchar(255) character set unicode_fss,
    out_appurl varchar(1024) character set unicode_fss,
    out_method smallint,
    out_postdata varchar(1024) character set unicode_fss,
    out_popup integer,
    out_clientwebbrowser smallint,
    out_webbrowserwidth integer,
    out_webbrowserheight integer,
    out_shortcut smallint,
    out_ordervalue integer,
    out_createtime timestamp)
as
begin
  if (in_userid > 0) then
  begin
      for select APPID, FORUSERID, FORUSERTYPE, APPNAME, CATEGORY, INTRODUCTION, APPIMAGE,  APPURL, 
           METHOD, POSTDATA, POPUP, CLIENTWEBBROWSER, WEBBROWSERWIDTH, WEBBROWSERHEIGHT, SHORTCUT, ORDERVALUE, CREATETIME
           from WEBAPPS where (FORUSERID = :in_userid) or (FORUSERTYPE = :in_usertype) order by ORDERVALUE into :out_appid, 
           :out_foruserid, :out_forusertype, :out_appname, :out_category, :out_introduction, :out_appimage, 
           :out_appurl, :out_method, :out_postdata, :out_popup, :out_clientwebbrowser, 
           :out_webbrowserwidth, :out_webbrowserheight, :out_shortcut, :out_ordervalue, :out_createtime 
        do
      begin
        suspend;
      end
  end
  else
  begin
      for select APPID, FORUSERID, FORUSERTYPE, APPNAME, CATEGORY, INTRODUCTION, APPIMAGE,  APPURL, 
           METHOD, POSTDATA, POPUP, CLIENTWEBBROWSER, WEBBROWSERWIDTH, WEBBROWSERHEIGHT, SHORTCUT, ORDERVALUE, CREATETIME
           from WEBAPPS where (FORUSERID = 0) and (FORUSERTYPE = :in_usertype) order by ORDERVALUE into :out_appid, 
           :out_foruserid, :out_forusertype, :out_appname, :out_category, :out_introduction, :out_appimage, 
           :out_appurl, :out_method, :out_postdata, :out_popup, :out_clientwebbrowser, 
           :out_webbrowserwidth, :out_webbrowserheight, :out_shortcut, :out_ordervalue, :out_createtime 
        do
      begin
        suspend;
      end
  end
end##

/* ADDRECENTITEM */

CREATE OR ALTER PROCEDURE ADDRECENTITEM (
    in_userid integer,
    in_itemtype smallint,
    in_itemvalue varchar(255) character set unicode_fss)
returns (
    out_sid bigint,
    out_userid integer,
    out_itemtype smallint,
    out_itemvalue varchar(255) character set unicode_fss,
    out_createtime timestamp)
as
begin
  if (exists(select * from RECENTITEMS where (USERID = :in_userid) and (ITEMTYPE = :in_itemtype) and (ITEMVALUE = :in_itemvalue))) then
  begin
    update RECENTITEMS set CREATETIME = CURRENT_TIMESTAMP 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,
      CURRENT_TIMESTAMP
      );
  end

  select SID, USERID, ITEMTYPE, ITEMVALUE, CREATETIME
       from RECENTITEMS where (USERID = :in_userid) and (ITEMTYPE = :in_itemtype) and (ITEMVALUE = :in_itemvalue) into 
       :out_sid, :out_userid, :out_itemtype, :out_itemvalue, :out_createtime;

  suspend;
end##

/* REMOVERECENTITEM */

CREATE OR ALTER PROCEDURE REMOVERECENTITEM (
    in_userid integer,
    in_sid bigint)
as
begin
  delete from RECENTITEMS where SID = :in_sid and USERID = :in_userid;
end##

/* GETRECENTITEMS */

CREATE OR ALTER PROCEDURE GETRECENTITEMS (
    in_userid integer)
returns (
    out_sid bigint,
    out_userid integer,
    out_itemtype smallint,
    out_itemvalue varchar(255) character set unicode_fss,
    out_createtime timestamp)
as
begin
  for select SID, USERID, ITEMTYPE, ITEMVALUE, CREATETIME
       from RECENTITEMS where USERID = :in_userid order by CREATETIME DESC into 
       :out_sid, :out_userid, :out_itemtype, :out_itemvalue, :out_createtime 
    do
  begin
    suspend;
  end
end##

/* ADDOFFLINEFILES */

CREATE OR ALTER PROCEDURE ADDOFFLINEFILES (
    in_sid varchar(50) character set ascii,
    in_sender_userid integer,
    in_receiver_userid integer,
    in_filenameordir varchar(255) character set unicode_fss,
    in_isdir smallint,
    in_allfilecount integer,
    in_allfilesize bigint)
as
begin
  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,
    CURRENT_TIMESTAMP
    );
end##

/* REMOVEOFFLINEFILES */

CREATE OR ALTER PROCEDURE REMOVEOFFLINEFILES (
    in_sid varchar(50) character set ascii)
as
begin
  delete from OFFLINEFILES where SID = :in_sid;
end##

/* GETOFFLINEFILES */

CREATE OR ALTER PROCEDURE GETOFFLINEFILES (
    in_userid integer)
returns (
    out_sid varchar(50) character set ascii,
    out_sender_userid integer,
    out_receiver_userid integer,
    out_filenameordir varchar(255) character set unicode_fss,
    out_isdir smallint,
    out_allfilecount integer,
    out_allfilesize bigint,
    out_createtime timestamp)
as
begin
  for select SID, SENDER_USERID, RECEIVER_USERID, FILENAMEORDIR, 
    ISDIR, ALLFILECOUNT, ALLFILESIZE, CREATETIME 
    from OFFLINEFILES where RECEIVER_USERID = :in_userid into
    :out_sid, :out_sender_userid, :out_receiver_userid, :out_filenameordir,
    :out_isdir, :out_allfilecount, :out_allfilesize, :out_createtime 
    do
  begin
    suspend;
  end
end##

/* ADDSYSMSG */

CREATE OR ALTER PROCEDURE ADDSYSMSG (
    in_title varchar(50) character set unicode_fss,
    in_category varchar(50) character set unicode_fss,
    in_content varchar(1024) character set unicode_fss,
    in_link varchar(255) character set unicode_fss,
    in_attachfile varchar(255) character set unicode_fss,
    in_forusertype smallint,
    in_fordepartid integer, 
    in_fordepartname varchar(50) character set unicode_fss,
    in_sendtonum integer,
    in_sendtodetail varchar(255) character set unicode_fss,
    in_foronlineusers smallint,
    in_needreaded smallint,
    in_needfeedback smallint,
    in_sendtime timestamp)
returns(
    out_sysmsgid bigint)
as
    declare variable var_datebefore timestamp;
    declare variable var_tmpsysmsgid bigint;
    declare variable var_newsysmsgid bigint;
begin
  var_datebefore = addDay(CURRENT_TIMESTAMP, -30);
  for select SYSMSGID from SYSMSGS where SENDTIME < :var_datebefore into :var_tmpsysmsgid do
  begin
    delete from SYSMSGDETAILS where SYSMSGID = :var_tmpsysmsgid;
  end
  delete from SYSMSGS where SENDTIME < :var_datebefore;
  
  select max(SYSMSGID) from SYSMSGS into :var_newsysmsgid;
  if (var_newsysmsgid is null) then var_newsysmsgid = 0;
  var_newsysmsgid = var_newsysmsgid + 1;

  out_sysmsgid = var_newsysmsgid;

  insert into SYSMSGS(
    SYSMSGID, 
    TITLE, 
    CATEGORY,
    CONTENT, 
    LINK, 
    ATTACHFILE,
    FORUSERTYPE, 
    FORDEPARTID,
    FORDEPARTNAME,
    SENDTONUM,
    SENDTODETAIL,
    FORONLINEUSERS,
    NEEDREADED, 
    NEEDFEEDBACK, 
    READEDCOUNT,
    FEEDBACKCOUNT,
    SENDTIME
    ) values(
    :out_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
    );
  
  suspend;
end##

/* GETUSERSYSMSGS */

CREATE OR ALTER PROCEDURE GETUSERSYSMSGS (
    in_userid integer)
returns(
    out_sysmsgid bigint,
    out_title varchar(50) character set unicode_fss,
    out_category varchar(50) character set unicode_fss,
    out_content varchar(1024) character set unicode_fss,
    out_link varchar(255) character set unicode_fss,
    out_attachfile varchar(255) character set unicode_fss,
    out_forusertype smallint,
    out_fordepartid integer, 
    out_fordepartname varchar(50) character set unicode_fss,
    out_sendtonum integer,
    out_sendtodetail varchar(255) character set unicode_fss,
    out_foronlineusers smallint,
    out_needreaded smallint,
    out_needfeedback smallint,
    out_readedcount integer,
    out_feedbackcount integer,
    out_sendtime timestamp)
as
    declare variable var_usertype smallint;
    declare variable var_departid integer;
    declare variable var_lastrecvsysmsgs timestamp;
begin
  select USERTYPE, DEPARTID, LASTRECVSYSMSGS from USERS 
    where USERID = :in_userid into :var_usertype, :var_departid, :var_lastrecvsysmsgs;
    
  if ((var_usertype is null) or (var_departid is null)) then
  begin
    suspend;
    EXIT;
  end

  if (var_lastrecvsysmsgs is null) then
  begin
    var_lastrecvsysmsgs = addDay(CURRENT_TIMESTAMP, -30);
  end
  
  for select first 15 SYSMSGID, TITLE, CATEGORY, CONTENT, LINK, ATTACHFILE, 
    FORUSERTYPE, FORDEPARTID, FORDEPARTNAME, SENDTONUM, SENDTODETAIL, 
    FORONLINEUSERS, NEEDREADED, NEEDFEEDBACK, READEDCOUNT, FEEDBACKCOUNT, 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)
        )
    ) into 
    :out_sysmsgid, :out_title, :out_category, :out_content, :out_link, :out_attachfile,
    :out_forusertype, :out_fordepartid, :out_fordepartname, :out_sendtonum, :out_sendtodetail, 
    :out_foronlineusers, :out_needreaded, :out_needfeedback, :out_readedcount, 
    :out_feedbackcount, :out_sendtime do
  begin
    suspend;
  end

  update USERS set LASTRECVSYSMSGS = CURRENT_TIMESTAMP where USERID = :in_userid;
end##

/* GETSYSMSGS */

CREATE OR ALTER PROCEDURE GETSYSMSGS (
    in_pagesize integer,
    in_pageindex integer)
returns(
    out_maxpageindex integer,
    out_sysmsgid bigint,
    out_title varchar(50) character set unicode_fss,
    out_category varchar(50) character set unicode_fss,
    out_content varchar(1024) character set unicode_fss,
    out_link varchar(255) character set unicode_fss,
    out_attachfile varchar(255) character set unicode_fss,
    out_forusertype smallint,
    out_fordepartid integer, 
    out_fordepartname varchar(50) character set unicode_fss,
    out_sendtonum integer,
    out_sendtodetail varchar(255) character set unicode_fss,
    out_foronlineusers smallint,
    out_needreaded smallint,
    out_needfeedback smallint,
    out_readedcount integer,
    out_feedbackcount integer,
    out_sendtime timestamp)
as
  declare variable var_sysmsgcount integer;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
begin
  select count(*) from SYSMSGS where 1 = 1 into :var_sysmsgcount;
  out_maxpageindex = var_sysmsgcount / :in_pagesize;
  if (Mod(var_sysmsgcount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

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

  var_where_sql = '1 = 1';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' order by SENDTIME DESC rows ' ||
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' ||
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end
  else
  begin
    var_where_sql = var_where_sql || ' order by SENDTIME DESC ';
  end

  for EXECUTE STATEMENT 'select SYSMSGID, TITLE, CATEGORY, CONTENT, LINK, ATTACHFILE, ' || 
    ' FORUSERTYPE, FORDEPARTID, FORDEPARTNAME, SENDTONUM, SENDTODETAIL, ' || 
    ' FORONLINEUSERS, NEEDREADED, NEEDFEEDBACK, READEDCOUNT, FEEDBACKCOUNT, SENDTIME ' ||
    ' from SYSMSGS where ' || var_where_sql || ' ' into 
    :out_sysmsgid, :out_title, :out_category, :out_content, :out_link, :out_attachfile,
    :out_forusertype, :out_fordepartid, :out_fordepartname, :out_sendtonum, :out_sendtodetail, 
    :out_foronlineusers, :out_needreaded, :out_needfeedback, :out_readedcount, 
    :out_feedbackcount, :out_sendtime do
  begin
    suspend;
  end
end##

/* REMOVESYSMSG */

CREATE OR ALTER PROCEDURE REMOVESYSMSG (
    in_sysmsgid bigint)
as
begin
  delete from SYSMSGS where SYSMSGID = :in_sysmsgid;
  delete from SYSMSGDETAILS where SYSMSGID = :in_sysmsgid;
end##

/* ADDSYSMSGDETAIL */

CREATE OR ALTER PROCEDURE ADDSYSMSGDETAIL (
    in_sysmsgid bigint, 
    in_userid integer,
    in_readed smallint,
    in_feedback smallint,
    in_feedbacktext varchar(255) character set unicode_fss,
    in_sendtime timestamp)
as
begin
  insert into SYSMSGDETAILS(
    SYSMSGID, 
    USERID, 
    READED, 
    FEEDBACK, 
    FEEDBACKTEXT,
    SENDTIME
    ) values(
    :in_sysmsgid,
    :in_userid,
    :in_readed,
    :in_feedback,
    :in_feedbacktext,
    :in_sendtime
    );
end##

/* CHANGESYSMSGDETAIL_READED */

CREATE OR ALTER PROCEDURE CHANGESYSMSGDETAIL_READED (
    in_sysmsgid bigint, 
    in_userid integer)
as
  declare variable var_sendtime timestamp;
begin
  if (not exists(select * from SYSMSGDETAILS where SYSMSGID = :in_sysmsgid and USERID = :in_userid)) then
  begin
	  select SENDTIME from SYSMSGS where SYSMSGID = :in_sysmsgid into :var_sendtime;
  
	  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
end##

/* CHANGESYSMSGDETAIL_FEEDBACK */

CREATE OR ALTER PROCEDURE CHANGESYSMSGDETAIL_FEEDBACK (
    in_sysmsgid bigint, 
    in_userid integer,
    in_feedbacktext varchar(255) character set unicode_fss)
as
  declare variable var_sendtime timestamp;
begin
  if (not exists(select * from SYSMSGDETAILS where SYSMSGID = :in_sysmsgid and USERID = :in_userid)) then
  begin
	  select SENDTIME from SYSMSGS where SYSMSGID = :in_sysmsgid into :var_sendtime;
  
	  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
end##

/* GETSYSMSGDETAILS */

CREATE OR ALTER PROCEDURE GETSYSMSGDETAILS (
    in_sysmsgid bigint,
    in_pagesize integer,
    in_pageindex integer)
returns(
    out_maxpageindex integer,
    out_sysmsgid bigint,
    out_userid integer, 
    out_readed smallint,
    out_feedback smallint,
    out_feedbacktext varchar(255) character set unicode_fss,
    out_sendtime timestamp)
as
  declare variable var_itemcount integer;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
begin
  select count(*) from SYSMSGDETAILS where SYSMSGID = :in_sysmsgid into :var_itemcount;
  out_maxpageindex = var_itemcount / :in_pagesize;
  if (Mod(var_itemcount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

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

  var_where_sql = 'SYSMSGID = ' || cast(in_sysmsgid as varchar(10));

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' order by SENDTIME DESC rows ' ||
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' ||
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end
  else
  begin
    var_where_sql = var_where_sql || ' order by SENDTIME DESC ';
  end

  for EXECUTE STATEMENT 'select SYSMSGID, USERID, READED, FEEDBACK, FEEDBACKTEXT, SENDTIME ' ||
    ' from SYSMSGDETAILS where ' || var_where_sql || ' ' into 
    :out_sysmsgid, :out_userid, :out_readed, :out_feedback, :out_feedbacktext, :out_sendtime do
  begin
    suspend;
  end
end##

/* ADDSENDSMS */

CREATE OR ALTER PROCEDURE ADDSENDSMS (
    in_msgid varchar(50) character set ascii,
    in_userid integer,
    in_mobile varchar(20) character set unicode_fss,
    in_content varchar(4000) character set unicode_fss,
    in_receiver integer,
    in_sendtime timestamp)
as
begin
  delete from SENDSMS where CURRENT_TIMESTAMP > addDay(SENDTIME, 1);

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

/* GETSENDSMSLIST */

CREATE OR ALTER PROCEDURE GETSENDSMSLIST (
    in_userid integer)
returns (
    out_msgid varchar(50) character set ascii,
    out_userid integer,
    out_mobile varchar(20) character set unicode_fss,
    out_content varchar(4000) character set unicode_fss,
    out_receiver integer,
    out_sendtime timestamp)
as
begin
  if (in_userid > 0) then
  begin
      for select MSGID, USERID, MOBILE, CONTENT, RECEIVER, SENDTIME 
        from SENDSMS where USERID = :in_userid and CURRENT_TIMESTAMP < addDay(SENDTIME, 1) into
        :out_msgid, :out_userid, :out_mobile, :out_content, :out_receiver, :out_sendtime
        do
      begin
        suspend;
      end
  end
  else
  begin
      for select MSGID, USERID, MOBILE, CONTENT, RECEIVER, SENDTIME 
        from SENDSMS where CURRENT_TIMESTAMP < addDay(SENDTIME, 1) into
        :out_msgid, :out_userid, :out_mobile, :out_content, :out_receiver, :out_sendtime
        do
      begin
        suspend;
      end

  end
end##

/* ADDRECVSMS */

CREATE OR ALTER PROCEDURE ADDRECVSMS (
    in_msgid varchar(50) character set ascii,
    in_userid integer,
    in_mobile varchar(20) character set unicode_fss,
    in_content varchar(4000) character set unicode_fss,
    in_haveread smallint,
    in_sendtime timestamp)
as
begin
  delete from RECVSMS where CURRENT_TIMESTAMP > addDay(SENDTIME, 1);

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

/* GETRECVSMSLIST */

CREATE OR ALTER PROCEDURE GETRECVSMSLIST (
    in_userid integer)
returns (
    out_msgid varchar(50) character set ascii,
    out_userid integer,
    out_mobile varchar(20) character set unicode_fss,
    out_content varchar(4000) character set unicode_fss,
    out_haveread integer,
    out_sendtime timestamp)
as
begin
  if (in_userid > 0) then
  begin
      for select MSGID, USERID, MOBILE, CONTENT, HAVEREAD, SENDTIME 
        from RECVSMS where USERID = :in_userid and CURRENT_TIMESTAMP < addDay(SENDTIME, 1) into
        :out_msgid, :out_userid, :out_mobile, :out_content, :out_haveread, :out_sendtime
        do
      begin
        suspend;
      end

      update RECVSMS set HAVEREAD = 1 where USERID = :in_userid and CURRENT_TIMESTAMP < addDay(SENDTIME, 1);
  end
  else
  begin
      for select MSGID, USERID, MOBILE, CONTENT, HAVEREAD, SENDTIME 
        from RECVSMS where CURRENT_TIMESTAMP < addDay(SENDTIME, 1) into
        :out_msgid, :out_userid, :out_mobile, :out_content, :out_haveread, :out_sendtime
        do
      begin
        suspend;
      end

      update RECVSMS set HAVEREAD = 1 where CURRENT_TIMESTAMP < addDay(SENDTIME, 1);
  end
end##

/* ADDADMIN */

CREATE OR ALTER PROCEDURE ADDADMIN (
    in_adminname varchar(20) character set unicode_fss,
    in_adminrights varchar(255) character set unicode_fss,
    in_adminpwd varchar(255) character set unicode_fss,
    in_description varchar(255) character set unicode_fss,
    in_islock smallint)
returns (
    out_errorcode integer,
    out_adminname varchar(20) character set unicode_fss,
    out_adminrights varchar(255) character set unicode_fss,
    out_adminpwd varchar(255) character set unicode_fss,
    out_description varchar(255) character set unicode_fss,
    out_logined smallint,
    out_islock smallint,
    out_lastlogintime timestamp,
    out_createtime timestamp)
as
begin
  out_errorcode = 0;

  if (exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname))) then
  begin
    out_errorcode = 1;
    suspend;
    EXIT;
  end

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

  select ADMINNAME, ADMINRIGHTS, ADMINPWD, DESCRIPTION, LOGINED, ISLOCK, LASTLOGINTIME, CREATETIME 
       from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) into :out_adminname, :out_adminrights, 
       :out_adminpwd, :out_description, :out_logined, :out_islock, :out_lastlogintime, :out_createtime;

  suspend;
end##

/* MODIFYADMIN */

CREATE OR ALTER PROCEDURE MODIFYADMIN (
    in_adminname varchar(20) character set unicode_fss,
    in_adminrights varchar(255) character set unicode_fss,
    in_description varchar(255) character set unicode_fss,
    in_islock smallint)
as
begin
  update ADMINS set ADMINRIGHTS = :in_adminrights, DESCRIPTION = :in_description, ISLOCK = :in_islock
    where LOWER(ADMINNAME) = LOWER(:in_adminname);
end##

/* VALIDATEADMIN */

CREATE OR ALTER PROCEDURE VALIDATEADMIN (
    in_adminname varchar(20) character set unicode_fss,
    in_adminpwd varchar(255) character set unicode_fss)
returns (
    out_errorcode integer)
as
  declare variable var_islock smallint;
begin
  out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) and ADMINPWD = :in_adminpwd)) then
  begin
    out_errorcode = 1; /*---(not find admin) or (adminpwd error)---*/
    suspend;
    EXIT;
  end

  var_islock = 0;
  select ISLOCK from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) and ADMINPWD = :in_adminpwd into :var_islock;
  if (var_islock = 1) then
  begin
    out_errorcode = 2; /*---islock---*/
    suspend;
    EXIT;
  end

  suspend;
end##

/* ADMINLOGIN */

CREATE OR ALTER PROCEDURE ADMINLOGIN (
    in_adminname varchar(20) character set unicode_fss,
    in_adminpwd varchar(255) character set unicode_fss)
returns (
    out_errorcode integer)
as
  declare variable var_islock smallint;
begin
  out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) and ADMINPWD = :in_adminpwd)) then
  begin
    out_errorcode = 1; /*---(not find admin) or (adminpwd error)---*/
    suspend;
    EXIT;
  end

  var_islock = 0;
  select ISLOCK from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) and ADMINPWD = :in_adminpwd into :var_islock;
  if (var_islock = 1) then
  begin
    out_errorcode = 2; /*---islock---*/
    suspend;
    EXIT;
  end

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

  suspend;
end##

/* ADMINLOGOUT */

CREATE OR ALTER PROCEDURE ADMINLOGOUT (
    in_adminname varchar(20) character set unicode_fss)
as
begin
  update ADMINS set LOGINED = 0 where LOWER(ADMINNAME) = LOWER(:in_adminname);
end##

/* LOCKADMIN */

CREATE OR ALTER PROCEDURE LOCKADMIN (
    in_adminname varchar(20) character set unicode_fss,
    in_islock smallint)
as
begin
  update ADMINS set ISLOCK = :in_islock where LOWER(ADMINNAME) = LOWER(:in_adminname);
end##

/* CHANGEADMINPWD */

CREATE OR ALTER PROCEDURE CHANGEADMINPWD (
    in_adminname varchar(20) character set unicode_fss,
    in_oldpwd varchar(255) CHARACTER SET UNICODE_FSS,
    in_newpwd varchar(255) CHARACTER SET UNICODE_FSS)
returns (
    out_errorcode integer)
as
begin
  out_errorcode = 0;

  if (not exists(select * from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) and ADMINPWD = :in_oldpwd)) then
  begin
    out_errorcode = 1; /*---(not find admin) or (oldpwd error)---*/
    suspend;
    EXIT;
  end

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

  suspend;
end##

/* REMOVEADMIN */

CREATE OR ALTER PROCEDURE REMOVEADMIN (
    in_adminname varchar(20) character set unicode_fss)
as
begin
  delete from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname);
end##

/* GETADMIN */

CREATE OR ALTER PROCEDURE GETADMIN (
    in_adminname varchar(20) character set unicode_fss)
returns (
    out_adminname varchar(20) character set unicode_fss,
    out_adminrights varchar(255) character set unicode_fss,
    out_description varchar(255) character set unicode_fss,
    out_logined smallint,
    out_islock smallint,
    out_lastlogintime timestamp,
    out_createtime timestamp)
as
begin
  select ADMINNAME, ADMINRIGHTS, DESCRIPTION, LOGINED, ISLOCK, LASTLOGINTIME, CREATETIME
       from ADMINS where LOWER(ADMINNAME) = LOWER(:in_adminname) into :out_adminname, :out_adminrights, 
       :out_description, :out_logined, :out_islock, :out_lastlogintime, :out_createtime;
  suspend;
end##

/* GETADMINS */

CREATE OR ALTER PROCEDURE GETADMINS
returns (
    out_adminname varchar(20) character set unicode_fss,
    out_adminrights varchar(255) character set unicode_fss,
    out_description varchar(255) character set unicode_fss,
    out_logined smallint,
    out_islock smallint,
    out_lastlogintime timestamp,
    out_createtime timestamp)
as
begin
  for select ADMINNAME, ADMINRIGHTS, DESCRIPTION, LOGINED, ISLOCK, LASTLOGINTIME, CREATETIME
       from ADMINS where 1 = 1 order by CREATETIME into :out_adminname, :out_adminrights, 
       :out_description, :out_logined, :out_islock, :out_lastlogintime, :out_createtime
    do
  begin
    suspend;
  end
end##

/* RESETLOGINEDADMINS */

CREATE OR ALTER PROCEDURE RESETLOGINEDADMINS
as
begin
  update ADMINS set LOGINED = 0 where LOGINED = 1;
end##

/* ADDADMINLOG */

CREATE OR ALTER PROCEDURE ADDADMINLOG (
    in_adminname varchar(20) character set unicode_fss,
    in_description varchar(255) character set unicode_fss,
    in_ipaddr varchar(20) character set unicode_fss)
returns (
    out_sid bigint,
    out_adminname varchar(20) character set unicode_fss,
    out_description varchar(255) character set unicode_fss,
    out_ipaddr varchar(20) character set unicode_fss,
    out_createtime timestamp)
as
begin
  delete from ADMINLOGS where CURRENT_TIMESTAMP > addDay(CREATETIME, 90);

  insert into ADMINLOGS(
    ADMINNAME,
    DESCRIPTION,
    IPADDR,
    CREATETIME
    )
    values(
    :in_adminname,
    :in_description,
    :in_ipaddr,
    CURRENT_TIMESTAMP
    );

  select SID, ADMINNAME, DESCRIPTION, IPADDR, CREATETIME 
       from ADMINLOGS where SID = GEN_ID(ADMINLOGS_GEN, 0) into :out_sid, :out_adminname,
       :out_description, :out_ipaddr, :out_createtime;

  suspend;
end##

/* GETADMINLOGS */

CREATE OR ALTER PROCEDURE GETADMINLOGS (
    in_pagesize integer,
    in_pageindex integer)
returns (
    out_maxpageindex integer,
    out_sid bigint,
    out_adminname varchar(20) character set unicode_fss,
    out_description varchar(255) character set unicode_fss,
    out_ipaddr varchar(20) character set unicode_fss,
    out_createtime timestamp)
as
  declare variable var_adminlogcount integer;
  declare variable var_where_sql varchar(1024) character set unicode_fss;
begin
  if (in_pagesize <= 0) then in_pagesize = 1;

  select count(*) from ADMINLOGS where 1 = 1 into :var_adminlogcount;
  out_maxpageindex = var_adminlogcount / :in_pagesize;
  if (Mod(var_adminlogcount, :in_pagesize) > 0) then
  begin
    out_maxpageindex = out_maxpageindex + 1;
  end

  if (in_pageindex <= 0) then in_pageindex = out_maxpageindex;

  var_where_sql = '1 = 1 order by CREATETIME DESC';

  if ((in_pagesize > 0) and (in_pageindex > 0)) then
  begin
    var_where_sql = var_where_sql || ' rows ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + 1) as varchar(10)) || ' to ' || 
    cast((:in_pagesize * (:in_pageindex - 1) + :in_pagesize) as varchar(10));
  end

  for EXECUTE STATEMENT 'select SID, ADMINNAME, DESCRIPTION, IPADDR, CREATETIME ' ||
       'from ADMINLOGS where ' || var_where_sql || ' ' into 
       :out_sid, :out_adminname, :out_description, :out_ipaddr, :out_createtime
    do
  begin
    suspend;
  end
end##

/* ADDJOINREQUEST */

CREATE OR ALTER PROCEDURE ADDJOINREQUEST (
    in_userid integer,
    in_content varchar(255) character set unicode_fss,
    in_sender integer)
as
begin
  if (exists(select * from JOINREQUESTS where USERID = :in_userid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINREQUESTS */

CREATE OR ALTER PROCEDURE GETJOINREQUESTS (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_content varchar(255) character set unicode_fss,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, CONTENT, SENDER, SENDTIME 
    from JOINREQUESTS where USERID = :in_userid into 
    :out_sid, :out_userid, :out_content, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINREQUESTS where USERID = :in_userid;
end##

/* ADDJOINRESPONSE */

CREATE OR ALTER PROCEDURE ADDJOINRESPONSE (
    in_userid integer,
    in_content varchar(255) character set unicode_fss,
    in_agree smallint,
    in_sender integer)
as
begin
  if (exists(select * from JOINRESPONSES where USERID = :in_userid and SENDER = :in_sender)) then
  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, CURRENT_TIMESTAMP);
  end
end##

/* GETJOINRESPONSES */

CREATE OR ALTER PROCEDURE GETJOINRESPONSES (
    in_userid integer)
returns(
    out_sid bigint,
    out_userid integer,
    out_content varchar(255) character set unicode_fss,
    out_agree smallint,
    out_sender integer,
    out_sendtime timestamp)
as
begin
  for select SID, USERID, CONTENT, AGREE, SENDER, SENDTIME 
    from JOINRESPONSES where USERID = :in_userid into 
    :out_sid, :out_userid, :out_content, :out_agree, :out_sender, :out_sendtime 
    do
  begin
    suspend;
  end

  delete from JOINRESPONSES where USERID = :in_userid;
end##

/* ADDOFFLINEMESSAGE */

CREATE OR ALTER PROCEDURE ADDOFFLINEMESSAGE (
    in_msgid varchar(50) character set ascii,
    in_userid integer,
    in_msgtype smallint,
    in_msglevel smallint,
    in_content varchar(8192) character set unicode_fss,
    in_font varchar(50) character set unicode_fss,
    in_sender_userid integer,
    in_sender_nickname varchar(20) character set unicode_fss,
    in_sender_actualname varchar(20) character set unicode_fss)
as
begin
  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,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFLINEMESSAGE */

CREATE OR ALTER PROCEDURE REMOVEOFFLINEMESSAGE (
    in_msgid varchar(50) character set ascii,
    in_userid integer)
as
begin
  delete from OFFLINEMESSAGES where MSGID = :in_msgid and USERID = :in_userid;
end##

/* GETOFFLINEMESSAGES */

CREATE OR ALTER PROCEDURE GETOFFLINEMESSAGES (
    in_userid integer)
returns (
    out_msgid varchar(50) character set ascii,
    out_userid integer,
    out_msgtype smallint,
    out_msglevel smallint,
    out_content varchar(8192) character set unicode_fss,
    out_font varchar(50) character set unicode_fss,
    out_sender_userid integer,
    out_sender_nickname varchar(20) character set unicode_fss,
    out_sender_actualname varchar(20) character set unicode_fss,
    out_sendtime timestamp)
as
begin
  for select MSGID, USERID, MSGTYPE, MSGLEVEL, CONTENT, FONT, SENDER_USERID, SENDER_NICKNAME, 
      SENDER_ACTUALNAME, SENDTIME from OFFLINEMESSAGES
      where USERID = :in_userid order by SENDTIME ASC into 
      :out_msgid, :out_userid, out_msgtype, :out_msglevel, :out_content, :out_font, :out_sender_userid, 
      :out_sender_nickname, :out_sender_actualname, :out_sendtime
    do
  begin
    suspend;
  end
end##

/* ADDOFFLINESUPEROBJECT */

CREATE OR ALTER PROCEDURE ADDOFFLINESUPEROBJECT (
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer,
    in_superobjectcode varchar(1024) character set unicode_fss,
    in_localfilename varchar(1024) character set unicode_fss)
as
begin
  insert into OFFLINESUPEROBJECTS(
    MSGID,
    FROMUSERID,
    SUPEROBJECTCODE,
    LOCALFILENAME,
    CREATETIME
  ) values (
    :in_msgid,
    :in_fromuserid,
    :in_superobjectcode,
    :in_localfilename,
    CURRENT_TIMESTAMP
  );
end##

/* REMOVEOFFLINESUPEROBJECT */

CREATE OR ALTER PROCEDURE REMOVEOFFLINESUPEROBJECT (
    in_sid bigint)
as
begin
  delete from OFFLINESUPEROBJECTS where SID = :in_sid;
end##

/* GETOFFLINESUPEROBJECTS */

CREATE OR ALTER PROCEDURE GETOFFLINESUPEROBJECTS (
    in_msgid varchar(50) character set ascii,
    in_fromuserid integer)
returns (
    out_sid bigint,
    out_msgid varchar(50) character set unicode_fss,
    out_fromuserid integer,
    out_superobjectcode varchar(1024) character set unicode_fss,
    out_localfilename varchar(1024) character set unicode_fss,
    out_createtime timestamp)
as
begin
  for select SID, MSGID, FROMUSERID, SUPEROBJECTCODE, LOCALFILENAME, CREATETIME
    from OFFLINESUPEROBJECTS where MSGID = :in_msgid and FROMUSERID = :in_fromuserid into 
    :out_sid, :out_msgid, :out_fromuserid, :out_superobjectcode, :out_localfilename, :out_createtime do
  begin
    suspend;
  end
end##

/* GETUSERPUSH */

CREATE OR ALTER PROCEDURE GETUSERPUSH (
    in_userid integer)
returns (
    out_clientid varchar(50),
    out_os smallint,
    out_badge integer,
    out_needpush smallint,
    out_isactive smallint)
as
begin
  for select a.CLIENTID,a.OS,a.BADGE,a.NEEDPUSH,a.ISACTIVE
       from PUSH as a
       where a.USERID=:in_userid and a.ISACTIVE=1 and a.NEEDPUSH=1 into
       :out_clientid,:out_os,:out_badge,:out_needpush,:out_isactive
    do
  begin
    suspend;
  end
end##

/* UPDATEUSERPUSH */

CREATE OR ALTER PROCEDURE UPDATEUSERPUSH (
    in_userid integer,
    in_clientid varchar(50),
    in_device varchar(50),
    in_os smallint,
    in_badge integer,
    in_iosdevicetoken varchar(100),
    in_needpush smallint)
as
declare variable var_newpushid integer;
begin

  if (:in_clientid <>'') then
  begin
	  if (exists (select * from PUSH where USERID = :in_userid and CLIENTID= :in_clientid)) then
	  begin
		  update PUSH set
			BADGE = :in_badge,
			ISACTIVE = 1,
			NEEDPUSH = :in_needpush
			where USERID = :in_userid and CLIENTID= :in_clientid;
	  end
	  else
	  begin
		  select max(PUSHID) from PUSH into :var_newpushid;
		  if (var_newpushid is null) then var_newpushid = 1;
		  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,
			CURRENT_TIMESTAMP
			);

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

/* INCPUSHBADGE */

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

  end
end##