CREATE TABLE [dbo].[ACTIVE_USER] (
	[USER_ID] [int] NOT NULL ,
	[SID] [char] (36) NOT NULL ,
	[STATUS] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[AUTH_LIST] (
	[FROM_USER_ID] [int] NOT NULL ,
	[TO_USER_ID] [int] NOT NULL ,
	[MESS_TEXT] [nvarchar] (1024) NOT NULL ,
	[DENY_FLAG] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BINARY_DATA] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[FID] [char] (36) NOT NULL ,
	[FileData] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[CHAT_MESS] (
	[CHAT_MESS_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CHAT_ID] [int] NOT NULL ,
	[MID] [char] (36) NOT NULL ,
	[SEND_TIME] [int] NULL ,
	[MESS_TEXT] [ntext] NULL ,
	[USER_ID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[CHAT_USERS] (
	[CHAT_USER_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[CHAT_ID] [int] NOT NULL ,
	[USER_ID] [int] NOT NULL ,
	[ACCEPTED] [int] NOT NULL ,
	[EXITED] [bit] NOT NULL ,
	[USER_STATUS] [tinyint] NOT NULL ,
	[INVITE_TEXT] [nvarchar] (1024) NULL ,
	[FROM_USER_ID] [int] NOT NULL ,
	[TIME] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CHATS] (
	[CHAT_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[NAME] [nvarchar] (50) NULL ,
	[DESC] [nvarchar] (1000) NULL ,
	[CUID] [char] (36) NOT NULL ,
	[OWNER_ID] [int] NOT NULL ,
	[BEGIN_TIME] [int] NULL ,
	[END_TIME] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[COMPANIES] (
	[company_id] [int] IDENTITY (1, 1) NOT NULL ,
	[company_name] [nvarchar] (100) NULL ,
	[domain] [nvarchar] (255) NOT NULL ,
	[db_name] [varchar] (100) NOT NULL ,
	[app_id] [varchar] (100) NOT NULL ,
	[description] [ntext] NULL ,
	[contact_name] [nvarchar] (100) NULL ,
	[contact_phone] [nvarchar] (100) NULL ,
	[contact_email] [nvarchar] (100) NULL ,
	[support_name] [nvarchar] (100) NULL ,
	[support_email] [nvarchar] (100) NULL ,
	[product_name] [nvarchar] (50) NULL ,
	[company_logo] [image] NULL ,
	[theme] [nvarchar] (50) NOT NULL ,
	[use_im] [bit] NOT NULL ,
	[title1] [nvarchar] (500) NULL ,
	[title2] [nvarchar] (500) NULL ,
	[text1] [ntext] NULL ,
	[text2] [ntext] NULL ,
	[hp_image] [image] NULL ,
	[max_users] [int] NOT NULL ,
	[max_external_users] [int] NOT NULL ,
	[is_global] [bit] NULL ,
	[creation_date] [datetime] NOT NULL ,
	[company_type] [tinyint] NOT NULL ,
	[start_date] [datetime] NULL ,
	[end_date] [datetime] NULL ,
	[rate_per_user] [int] NOT NULL ,
	[is_active] [bit] NOT NULL ,
	[max_disk_space] [int] NOT NULL ,
	[enable_alerts] [bit] NOT NULL ,
	[show_admin_wizard] [bit] NOT NULL ,
	[use_ssl] [bit] NOT NULL ,
	[FirstDayOfWeek] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[CONTACT_LIST] (
	[USER_ID] [int] NOT NULL ,
	[CONT_USER_ID] [int] NOT NULL ,
	[LIST_TYPE] [int] NOT NULL ,
	[HAS_AUTHORIZATION] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DEPENDENCES] (
	[IMGROUP_ID] [int] NOT NULL ,
	[DEP_IMGROUP_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DL_CATEGORIES] (
	[CategoryId] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (100) NOT NULL ,
	[Description] [ntext] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[DL_PRODUCT_CATEGORIES] (
	[ProductId] [int] NOT NULL ,
	[CategoryId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DL_PRODUCTS] (
	[ProductId] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductGuid] [char] (36) NOT NULL ,
	[Title] [nvarchar] (100) NOT NULL ,
	[Description] [nvarchar] (3000) NULL ,
	[Locale] [char] (5) NOT NULL ,
	[Os] [nvarchar] (100) NOT NULL ,
	[FileName] [nvarchar] (100) NOT NULL ,
	[AutoInstall] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DL_UPDATES] (
	[UpdateId] [int] IDENTITY (1, 1) NOT NULL ,
	[VersionId] [int] NOT NULL ,
	[Description] [nvarchar] (1000) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DL_VERSIONS] (
	[VersionId] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductId] [int] NOT NULL ,
	[Build] [int] NOT NULL ,
	[Date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FILE] (
	[FROM_USER_ID] [int] NOT NULL ,
	[TO_USER_ID] [int] NOT NULL ,
	[FILE_DESCR] [nvarchar] (1024) NOT NULL ,
	[FILE_ID] [char] (36) NOT NULL ,
	[FILE_NAME] [nvarchar] (255) NOT NULL ,
	[FILE_STATUS] [int] NOT NULL ,
	[FILE_DATE] [int] NOT NULL ,
	[FILE_SIZE] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[IMGROUPS] (
	[IMGROUP_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[COMPANY_ID] [int] NOT NULL ,
	[IMGROUP_NAME] [nvarchar] (50) NOT NULL ,
	[CLIENT_LOGO] [image] NULL ,
	[COLOR] [char] (6) NOT NULL ,
	[LOGO_VERSION] [int] NOT NULL ,
	[IS_INTERNAL] [bit] NOT NULL ,
	[is_global] [bit] NOT NULL ,
	[IS_DEFAULT] [bit] NOT NULL ,
	[IS_PARTNER] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[PORTAL_SESSIONS] (
	[USER_ID] [int] NOT NULL ,
	[DT_BEGIN] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SESSIONS] (
	[USER_ID] [int] NOT NULL ,
	[DT_BEGIN] [int] NULL ,
	[DT_END] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIAL_NOTIFICATIONS] (
	[company_id] [int] NOT NULL ,
	[after_week] [bit] NOT NULL ,
	[before_week] [bit] NOT NULL ,
	[before_day] [bit] NOT NULL ,
	[after_day_im] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIAL_PROFILES] (
	[ProfileID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProfileName] [nvarchar] (50) NOT NULL ,
	[IsActive] [bit] NOT NULL ,
	[ManualActivation] [bit] NOT NULL ,
	[MaxHDD] [int] NOT NULL ,
	[MaxUsers] [int] NOT NULL ,
	[MaxExternalUsers] [int] NOT NULL ,
	[TrialPeriod] [int] NOT NULL ,
	[UseIM] [bit] NOT NULL ,
	[AdminEmail] [nvarchar] (100) NOT NULL ,
	[ParentDomain] [nvarchar] (100) NOT NULL ,
	[IpAddress] [nvarchar] (15) NOT NULL ,
	[ServerLink] [nvarchar] (100) NOT NULL ,
	[EmailFrom] [nvarchar] (100) NOT NULL ,
	[EmailTrial] [nvarchar] (100) NOT NULL ,
	[UseAlertService] [bit] NOT NULL ,
	[AlertServiceURL] [nvarchar] (255) NOT NULL ,
	[SMTPServer] [nvarchar] (255) NOT NULL ,
	[ResellerTemplate] [ntext] NULL ,
	[ResellerBanner] [image] NULL ,
	[TrialTopLogo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIAL_REQUESTS] (
	[RequestID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (100) NOT NULL ,
	[SizeOfGroup] [nvarchar] (50) NULL ,
	[Description] [nvarchar] (100) NULL ,
	[Domain] [nvarchar] (255) NOT NULL ,
	[FirstName] [nvarchar] (100) NOT NULL ,
	[LastName] [nvarchar] (100) NOT NULL ,
	[Email] [nvarchar] (100) NOT NULL ,
	[Phone] [nvarchar] (50) NULL ,
	[Country] [nvarchar] (100) NULL ,
	[TimeZone] [nvarchar] (100) NULL ,
	[Login] [nvarchar] (50) NOT NULL ,
	[Password] [nvarchar] (50) NOT NULL ,
	[ResellerId] [int] NOT NULL ,
	[XML] [nvarchar] (4000) NULL ,
	[UseIM] [bit] NOT NULL ,
	[GUID] [uniqueidentifier] NOT NULL ,
	[IsActive] [bit] NOT NULL ,
	[IsDeleted] [bit] NOT NULL ,
	[CompanyID] [int] NULL ,
	[CreationDate] [datetime] NOT NULL ,
	[Locale] [nvarchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIAL_REQUESTS_FAILED] (
	[RequestID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [nvarchar] (100) NULL ,
	[SizeOfGroup] [nvarchar] (50) NULL ,
	[Description] [nvarchar] (100) NULL ,
	[Domain] [nvarchar] (255) NULL ,
	[FirstName] [nvarchar] (100) NULL ,
	[LastName] [nvarchar] (100) NULL ,
	[Email] [nvarchar] (100) NULL ,
	[Phone] [nvarchar] (50) NULL ,
	[Country] [nvarchar] (100) NULL ,
	[TimeZone] [nvarchar] (100) NULL ,
	[Login] [nvarchar] (50) NULL ,
	[Password] [nvarchar] (50) NULL ,
	[ResellerId] [int] NULL ,
	[ErrorCode] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TRIAL_RESELLERS] (
	[ResellerId] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (255) NOT NULL ,
	[Guid] [uniqueidentifier] NOT NULL ,
	[ContactName] [nvarchar] (255) NULL ,
	[ContactEmail] [nvarchar] (100) NULL ,
	[ContactPhone] [nvarchar] (50) NULL ,
	[IsDefault] [bit] NULL ,
	[CommissionPercentage] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[USER] (
	[USER_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[IMGROUP_ID] [int] NOT NULL ,
	[FIRST_NAME] [nvarchar] (50) NOT NULL ,
	[LAST_NAME] [nvarchar] (50) NOT NULL ,
	[LOGIN] [nvarchar] (250) NOT NULL ,
	[PASSWORD] [nvarchar] (50) NOT NULL ,
	[EMAIL] [nvarchar] (250) NULL ,
	[IS_ACTIVE] [bit] NOT NULL ,
	[stubs_version] [int] NOT NULL ,
	[status_time] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[USER_MESS] (
	[FROM_USER_ID] [int] NOT NULL ,
	[TO_USER_ID] [int] NOT NULL ,
	[MESS_TEXT] [ntext] NOT NULL ,
	[MESS_ID] [char] (36) NOT NULL ,
	[SEND_TIME] [int] NULL ,
	[FROM_USER_SID] [char] (36) NULL ,
	[TO_USER_SID] [char] (36) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ACTIVE_USER] WITH NOCHECK ADD
	CONSTRAINT [PK_ACTIVE_USER] PRIMARY KEY  CLUSTERED
	(
		[USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[AUTH_LIST] WITH NOCHECK ADD
	CONSTRAINT [PK_AUTH_LIST] PRIMARY KEY  CLUSTERED
	(
		[FROM_USER_ID],
		[TO_USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[BINARY_DATA] WITH NOCHECK ADD
	CONSTRAINT [PK_BINARY_DATA] PRIMARY KEY  CLUSTERED
	(
		[FID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHAT_MESS] WITH NOCHECK ADD
	CONSTRAINT [PK_CHAT_MESS] PRIMARY KEY  CLUSTERED
	(
		[CHAT_MESS_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHAT_USERS] WITH NOCHECK ADD
	CONSTRAINT [PK_CHAT_USERS] PRIMARY KEY  CLUSTERED
	(
		[CHAT_USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHATS] WITH NOCHECK ADD
	CONSTRAINT [PK_CHATS] PRIMARY KEY  CLUSTERED
	(
		[CHAT_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[COMPANIES] WITH NOCHECK ADD
	CONSTRAINT [PK_COMPANIES] PRIMARY KEY  CLUSTERED
	(
		[company_id]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[CONTACT_LIST] WITH NOCHECK ADD
	CONSTRAINT [PK_CONTACT_LIST] PRIMARY KEY  CLUSTERED
	(
		[USER_ID],
		[CONT_USER_ID],
		[LIST_TYPE]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[DEPENDENCES] WITH NOCHECK ADD
	CONSTRAINT [PK_DEPENDENCES] PRIMARY KEY  CLUSTERED
	(
		[IMGROUP_ID],
		[DEP_IMGROUP_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[DL_CATEGORIES] WITH NOCHECK ADD
	CONSTRAINT [PK_DL_CATEGORIES] PRIMARY KEY  CLUSTERED
	(
		[CategoryId]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[DL_PRODUCTS] WITH NOCHECK ADD
	CONSTRAINT [PK_DL_PRODUCTS] PRIMARY KEY  CLUSTERED
	(
		[ProductId]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[DL_VERSIONS] WITH NOCHECK ADD
	CONSTRAINT [PK_DL_VERSIONS] PRIMARY KEY  CLUSTERED
	(
		[VersionId]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[FILE] WITH NOCHECK ADD
	CONSTRAINT [PK_FILE] PRIMARY KEY  CLUSTERED
	(
		[FILE_ID],
		[TO_USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[IMGROUPS] WITH NOCHECK ADD
	CONSTRAINT [PK_IMGROUPS] PRIMARY KEY  CLUSTERED
	(
		[IMGROUP_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[TRIAL_REQUESTS_FAILED] WITH NOCHECK ADD
	CONSTRAINT [PK_TRIAL_REQUESTS_FAILED] PRIMARY KEY  CLUSTERED
	(
		[RequestID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[TRIAL_RESELLERS] WITH NOCHECK ADD
	CONSTRAINT [PK_TRIAL_RESELLERS] PRIMARY KEY  CLUSTERED
	(
		[ResellerId]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[USER] WITH NOCHECK ADD
	CONSTRAINT [PK_USER] PRIMARY KEY  CLUSTERED
	(
		[USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[USER_MESS] WITH NOCHECK ADD
	CONSTRAINT [PK_USER_MESS] PRIMARY KEY  CLUSTERED
	(
		[MESS_ID],
		[TO_USER_ID]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[ACTIVE_USER] ADD
	CONSTRAINT [DF_ACTIVE_USER_SID] DEFAULT (convert(char(36),newid())) FOR [SID],
	CONSTRAINT [DF_ACTIVE_USER_STATUS] DEFAULT (1) FOR [STATUS],
	CONSTRAINT [IX_ACTIVE_USER_1] UNIQUE  NONCLUSTERED
	(
		[USER_ID],
		[STATUS]
	)  ON [PRIMARY]
GO
 CREATE  INDEX [IX_ACTIVE_USER] ON [dbo].[ACTIVE_USER]([USER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AUTH_LIST] ADD
	CONSTRAINT [DF_AUTH_LIST_MESS_TEXT] DEFAULT ('') FOR [MESS_TEXT],
	CONSTRAINT [DF_AUTH_LIST_DENY_FLAG] DEFAULT (0) FOR [DENY_FLAG],
	CONSTRAINT [CK_AUTH_LIST] CHECK ([from_user_id] <> [to_user_id])
GO
 CREATE  INDEX [IX_AUTH_LIST] ON [dbo].[AUTH_LIST]([FROM_USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_AUTH_LIST_1] ON [dbo].[AUTH_LIST]([TO_USER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHAT_MESS] ADD
	CONSTRAINT [DF_CHAT_MESS_MID] DEFAULT (convert(char(36),newid())) FOR [MID]
GO
 CREATE  INDEX [IX_CHAT_MESS] ON [dbo].[CHAT_MESS]([CHAT_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_MESS_1] ON [dbo].[CHAT_MESS]([MID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_MESS_2] ON [dbo].[CHAT_MESS]([USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_MESS_3] ON [dbo].[CHAT_MESS]([SEND_TIME]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHAT_USERS] ADD
	CONSTRAINT [DF_CHAT_USERS_ACCEPTED] DEFAULT (0) FOR [ACCEPTED],
	CONSTRAINT [DF_CHAT_USERS_EXITED] DEFAULT (0) FOR [EXITED],
	CONSTRAINT [DF_CHAT_USERS_USER_STATUS] DEFAULT (0) FOR [USER_STATUS],
	CONSTRAINT [DF_CHAT_USERS_TIME] DEFAULT (0) FOR [TIME],
	CONSTRAINT [IX_CHAT_USERS_2] UNIQUE  NONCLUSTERED
	(
		[CHAT_ID],
		[USER_ID]
	)  ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_USERS] ON [dbo].[CHAT_USERS]([CHAT_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_USERS_1] ON [dbo].[CHAT_USERS]([USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHAT_USERS_3] ON [dbo].[CHAT_USERS]([FROM_USER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CHATS] ADD
	CONSTRAINT [DF_CHATS_CUID] DEFAULT (convert(char(36),newid())) FOR [CUID],
	CONSTRAINT [IX_CHATS] UNIQUE  NONCLUSTERED
	(
		[CUID]
	)  ON [PRIMARY]
GO
 CREATE  INDEX [IX_CHATS_1] ON [dbo].[CHATS]([OWNER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[COMPANIES] ADD
	CONSTRAINT [DF_COMPANIES_theme] DEFAULT ('XP') FOR [theme],
	CONSTRAINT [DF_COMPANIES_use_im] DEFAULT (1) FOR [use_im],
	CONSTRAINT [DF_COMPANIES_max_users] DEFAULT ((-1)) FOR [max_users],
	CONSTRAINT [DF_COMPANIES_max_external_users] DEFAULT ((-1)) FOR [max_external_users],
	CONSTRAINT [DF__COMPANIES__is_gl__345EC57D] DEFAULT (0) FOR [is_global],
	CONSTRAINT [DF_COMPANIES_creation_date] DEFAULT (getutcdate()) FOR [creation_date],
	CONSTRAINT [DF_COMPANIES_company_type] DEFAULT (1) FOR [company_type],
	CONSTRAINT [DF_COMPANIES_start_date] DEFAULT (getutcdate()) FOR [start_date],
	CONSTRAINT [DF_COMPANIES_end_date] DEFAULT (dateadd(month,1,getdate())) FOR [end_date],
	CONSTRAINT [DF_COMPANIES_rate_per_user] DEFAULT (0) FOR [rate_per_user],
	CONSTRAINT [DF_COMPANIES_is_active] DEFAULT (1) FOR [is_active],
	CONSTRAINT [DF_COMPANIES_max_disk_space] DEFAULT ((-1)) FOR [max_disk_space],
	CONSTRAINT [DF_COMPANIES_enable_alerts] DEFAULT (1) FOR [enable_alerts],
	CONSTRAINT [DF_COMPANIES_show_admin_wizard] DEFAULT (1) FOR [show_admin_wizard],
	CONSTRAINT [DF_COMPANIES_use_ssl] DEFAULT (0) FOR [use_ssl],
	CONSTRAINT [DF_COMPANIES_FirstDayOfWeek] DEFAULT (1) FOR [FirstDayOfWeek],
	CONSTRAINT [IX_COMPANIES] UNIQUE  NONCLUSTERED
	(
		[domain]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[CONTACT_LIST] ADD
	CONSTRAINT [DF_CONTACT_LIST_HAS_AUTHORIZATION] DEFAULT (1) FOR [HAS_AUTHORIZATION],
	CONSTRAINT [CK_CONTACT_LIST] CHECK ([USER_ID] <> [CONT_USER_ID])
GO
 CREATE  INDEX [IX_CONTACT_LIST_1] ON [dbo].[CONTACT_LIST]([USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_CONTACT_LIST_2] ON [dbo].[CONTACT_LIST]([CONT_USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_DEPENDENCES] ON [dbo].[DEPENDENCES]([IMGROUP_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_DEPENDENCES_1] ON [dbo].[DEPENDENCES]([DEP_IMGROUP_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DL_CATEGORIES] ADD
	CONSTRAINT [DF_DL_CATEGORIES_Description] DEFAULT ('') FOR [Description]
GO
ALTER TABLE [dbo].[FILE] ADD
	CONSTRAINT [DF_USER_FILE_FILE_DESCR] DEFAULT ('') FOR [FILE_DESCR],
	CONSTRAINT [DF_USER_FILE_FILE_ID] DEFAULT (convert(char(36),newid())) FOR [FILE_ID],
	CONSTRAINT [DF_USER_FILE_FILE_NAME] DEFAULT ('') FOR [FILE_NAME]
GO
 CREATE  INDEX [IX_USER_FILE] ON [dbo].[FILE]([FROM_USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_USER_FILE_1] ON [dbo].[FILE]([TO_USER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IMGROUPS] ADD
	CONSTRAINT [DF_ROLE_COMPANY_ID] DEFAULT (1) FOR [COMPANY_ID],
	CONSTRAINT [DF_ROLE_COLOR] DEFAULT ('2B6087') FOR [COLOR],
	CONSTRAINT [DF_ROLE_LOGO_VERSION] DEFAULT (0) FOR [LOGO_VERSION],
	CONSTRAINT [DF_ROLE_IS_INTERNAL] DEFAULT (0) FOR [IS_INTERNAL],
	CONSTRAINT [DF__ROLE__is_global__3552E9B6] DEFAULT (0) FOR [is_global],
	CONSTRAINT [DF_IMGROUPS_IS_DEFAULT] DEFAULT (0) FOR [IS_DEFAULT],
	CONSTRAINT [DF_IMGROUPS_IS_PARTNER] DEFAULT (0) FOR [IS_PARTNER]
GO
 CREATE  INDEX [IX_IMGROUPS] ON [dbo].[IMGROUPS]([COMPANY_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_PORTAL_SESSIONS] ON [dbo].[PORTAL_SESSIONS]([USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_PORTAL_SESSIONS_1] ON [dbo].[PORTAL_SESSIONS]([DT_BEGIN]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_SESSIONS] ON [dbo].[SESSIONS]([USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_SESSIONS_1] ON [dbo].[SESSIONS]([DT_BEGIN]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_SESSIONS_2] ON [dbo].[SESSIONS]([DT_END]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TRIAL_NOTIFICATIONS] ADD
	CONSTRAINT [DF_TRIAL_NOTIFICATIONS_after_week] DEFAULT (0) FOR [after_week],
	CONSTRAINT [DF_TRIAL_NOTIFICATIONS_before_week] DEFAULT (0) FOR [before_week],
	CONSTRAINT [DF_TRIAL_NOTIFICATIONS_before_day] DEFAULT (0) FOR [before_day],
	CONSTRAINT [DF_TRIAL_NOTIFICATIONS_after_day_im] DEFAULT (0) FOR [after_day_im]
GO
ALTER TABLE [dbo].[TRIAL_PROFILES] ADD
	CONSTRAINT [DF_TRIAL_PROFILES_IsActive] DEFAULT (1) FOR [IsActive]
GO
ALTER TABLE [dbo].[TRIAL_REQUESTS] ADD
	CONSTRAINT [DF_TRIAL_REQUESTS_GUID] DEFAULT (newid()) FOR [GUID],
	CONSTRAINT [DF_TRIAL_REQUESTS_IsActive] DEFAULT (0) FOR [IsActive],
	CONSTRAINT [DF_TRIAL_REQUESTS_IsDeleted] DEFAULT (0) FOR [IsDeleted],
	CONSTRAINT [DF_TRIAL_REQUESTS_CreationDate] DEFAULT (getutcdate()) FOR [CreationDate],
	CONSTRAINT [DF_TRIAL_REQUESTS_Locale] DEFAULT (N'en-US') FOR [Locale],
	CONSTRAINT [IX_TRIAL_REQUESTS] UNIQUE  NONCLUSTERED
	(
		[Domain]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[TRIAL_RESELLERS] ADD
	CONSTRAINT [DF_TRIAL_RESELLERS_Guid] DEFAULT (newid()) FOR [Guid]
GO
ALTER TABLE [dbo].[USER] ADD
	CONSTRAINT [DF_USER_FIRST_NAME] DEFAULT ('') FOR [FIRST_NAME],
	CONSTRAINT [DF_USER_LAST_NAME] DEFAULT ('') FOR [LAST_NAME],
	CONSTRAINT [DF_USER_IS_ACTIVE] DEFAULT (1) FOR [IS_ACTIVE],
	CONSTRAINT [DF__USER__stubs_vers__61316BF4] DEFAULT (0) FOR [stubs_version],
	CONSTRAINT [DF_USER_status_time] DEFAULT (datediff(second,'1970-01-01 00:00',getutcdate())) FOR [status_time]
GO
 CREATE  INDEX [IX_USER_1] ON [dbo].[USER]([LOGIN]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_USER] ON [dbo].[USER]([IMGROUP_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_USER_2] ON [dbo].[USER]([PASSWORD]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[USER_MESS] ADD
	CONSTRAINT [DF_USER_MESS_MESS_TEXT] DEFAULT ('') FOR [MESS_TEXT],
	CONSTRAINT [DF_USER_MESS_MESS_ID] DEFAULT (convert(char(36),newid())) FOR [MESS_ID]
GO
 CREATE  INDEX [IX_USER_MESS] ON [dbo].[USER_MESS]([FROM_USER_ID]) ON [PRIMARY]
GO
 CREATE  INDEX [IX_USER_MESS_1] ON [dbo].[USER_MESS]([TO_USER_ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AUTH_LIST] ADD
	CONSTRAINT [FK_AUTH_LIST_USER] FOREIGN KEY
	(
		[FROM_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	),
	CONSTRAINT [FK_AUTH_LIST_USER1] FOREIGN KEY
	(
		[TO_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[CHAT_MESS] ADD
	CONSTRAINT [FK_CHAT_MESS_CHATS] FOREIGN KEY
	(
		[CHAT_ID]
	) REFERENCES [dbo].[CHATS] (
		[CHAT_ID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_CHAT_MESS_USER] FOREIGN KEY
	(
		[USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[CHAT_USERS] ADD
	CONSTRAINT [FK_CHAT_USERS_CHATS] FOREIGN KEY
	(
		[CHAT_ID]
	) REFERENCES [dbo].[CHATS] (
		[CHAT_ID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_CHAT_USERS_USER] FOREIGN KEY
	(
		[USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[CHATS] ADD
	CONSTRAINT [FK_CHATS_USER] FOREIGN KEY
	(
		[OWNER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[CONTACT_LIST] ADD
	CONSTRAINT [FK_CONTACT_LIST_USER] FOREIGN KEY
	(
		[USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	),
	CONSTRAINT [FK_CONTACT_LIST_USER1] FOREIGN KEY
	(
		[CONT_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[DEPENDENCES] ADD
	CONSTRAINT [FK_DEPENDENCES_IMGROUPS] FOREIGN KEY
	(
		[IMGROUP_ID]
	) REFERENCES [dbo].[IMGROUPS] (
		[IMGROUP_ID]
	),
	CONSTRAINT [FK_DEPENDENCES_IMGROUPS1] FOREIGN KEY
	(
		[DEP_IMGROUP_ID]
	) REFERENCES [dbo].[IMGROUPS] (
		[IMGROUP_ID]
	)
GO
ALTER TABLE [dbo].[DL_PRODUCT_CATEGORIES] ADD
	CONSTRAINT [FK_DL_PRODUCT_CATEGORIES_DL_CATEGORIES] FOREIGN KEY
	(
		[CategoryId]
	) REFERENCES [dbo].[DL_CATEGORIES] (
		[CategoryId]
	),
	CONSTRAINT [FK_DL_PRODUCT_CATEGORIES_DL_PRODUCTS] FOREIGN KEY
	(
		[ProductId]
	) REFERENCES [dbo].[DL_PRODUCTS] (
		[ProductId]
	) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[DL_UPDATES] ADD
	CONSTRAINT [FK_DL_UPDATES_DL_VERSIONS] FOREIGN KEY
	(
		[VersionId]
	) REFERENCES [dbo].[DL_VERSIONS] (
		[VersionId]
	)
GO
ALTER TABLE [dbo].[DL_VERSIONS] ADD
	CONSTRAINT [FK_DL_VERSIONS_DL_PRODUCTS] FOREIGN KEY
	(
		[ProductId]
	) REFERENCES [dbo].[DL_PRODUCTS] (
		[ProductId]
	) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[FILE] ADD
	CONSTRAINT [FK_FILE_USER] FOREIGN KEY
	(
		[FROM_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	),
	CONSTRAINT [FK_FILE_USER1] FOREIGN KEY
	(
		[TO_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[IMGROUPS] ADD
	CONSTRAINT [FK_IMGROUPS_COMPANIES] FOREIGN KEY
	(
		[COMPANY_ID]
	) REFERENCES [dbo].[COMPANIES] (
		[company_id]
	)
GO
ALTER TABLE [dbo].[PORTAL_SESSIONS] ADD
	CONSTRAINT [FK_PORTAL_SESSIONS_USER] FOREIGN KEY
	(
		[USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[SESSIONS] ADD
	CONSTRAINT [FK_SESSIONS_USER] FOREIGN KEY
	(
		[USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
ALTER TABLE [dbo].[TRIAL_REQUESTS] ADD
	CONSTRAINT [FK_TRIAL_REQUESTS_TRIAL_RESELLERS] FOREIGN KEY
	(
		[ResellerId]
	) REFERENCES [dbo].[TRIAL_RESELLERS] (
		[ResellerId]
	)
GO
ALTER TABLE [dbo].[TRIAL_REQUESTS_FAILED] ADD
	CONSTRAINT [FK_TRIAL_REQUESTS_FAILED_TRIAL_RESELLERS] FOREIGN KEY
	(
		[ResellerId]
	) REFERENCES [dbo].[TRIAL_RESELLERS] (
		[ResellerId]
	)
GO
ALTER TABLE [dbo].[USER] ADD
	CONSTRAINT [FK_USER_IMGROUPS] FOREIGN KEY
	(
		[IMGROUP_ID]
	) REFERENCES [dbo].[IMGROUPS] (
		[IMGROUP_ID]
	)
GO
ALTER TABLE [dbo].[USER_MESS] ADD
	CONSTRAINT [FK_USER_MESS_USER] FOREIGN KEY
	(
		[FROM_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	),
	CONSTRAINT [FK_USER_MESS_USER1] FOREIGN KEY
	(
		[TO_USER_ID]
	) REFERENCES [dbo].[USER] (
		[USER_ID]
	)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_ADD_DEPENDENCES
	@IMGROUP_ID as int ,
	@DEP_IMGROUP_ID as int
AS
BEGIN TRAN
	INSERT INTO DEPENDENCES (IMGROUP_ID, DEP_IMGROUP_ID) VALUES(@IMGROUP_ID, @DEP_IMGROUP_ID)
	IF @@error != 0
		GOTO err
	INSERT INTO Contact_List
		SELECT U1.[user_id], U2.[user_id] AS cont_user_id, 1 AS List_Type, 0 AS Has_Authorisation
		 FROM [User] U1, [User] U2
		 WHERE U1.imgroup_id = @IMGROUP_ID AND U2.imgroup_id = @DEP_IMGROUP_ID AND NOT EXISTS
			(SELECT * FROM Contact_List
			  WHERE [user_id] = U1.[user_id] AND cont_user_id = U2.[user_id] AND list_type = 1)
	IF @@error != 0
		GOTO err
COMMIT TRAN
RETURN
err:
	ROLLBACK TRAN
	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_ADD_IMGROUP
	@IMGROUP_ID as int ,
	@COMPANY_ID int,
	@IMGROUP_NAME as nvarchar(50) ,
	@COLOR as char(6),
	@IS_GLOBAL bit,
	@IS_PARTNER as bit,
	@retval int output
as
IF @IS_GLOBAL=1 AND NOT EXISTS(SELECT COMPANY_ID FROM COMPANIES WHERE IS_GLOBAL=1 AND COMPANY_ID = @COMPANY_ID)
	RETURN
IF EXISTS(SELECT * FROM IMGROUPS WHERE IMGROUP_ID = @IMGROUP_ID)
begin
	DECLARE @prev_color char(6)
	SELECT @prev_color = color FROM IMGROUPS WHERE IMGROUP_ID = @IMGROUP_ID
	IF @prev_color <> @COLOR
		UPDATE IMGROUPS SET logo_version = logo_version + 1 WHERE IMGROUP_ID = @IMGROUP_ID
	UPDATE IMGROUPS SET IMGROUP_NAME=@IMGROUP_NAME, COLOR = @COLOR, IS_GLOBAL=@IS_GLOBAL
	  WHERE IMGROUP_ID = @IMGROUP_ID
	select @retval = @IMGROUP_ID
end else begin
	INSERT INTO IMGROUPS (IMGROUP_NAME, COLOR, COMPANY_ID,IS_GLOBAL, IS_PARTNER)
	  VALUES(@IMGROUP_NAME, @COLOR, @COMPANY_ID, @IS_GLOBAL, @IS_PARTNER)
	select @retval = @@identity
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_ADD_USER
	@user_id as int ,
	@login as nvarchar(250) ,
	@password as nvarchar(50) ,
	@first_name as nvarchar(50) ,
	@last_name as nvarchar(50) ,
	@email as nvarchar(250) ,
	@imgroup_id as int ,
	@is_active as bit,
	@retval int output
AS
SET NOCOUNT ON
DECLARE @RECREATE bit
SET @RECREATE = 1
DECLARE @old_imgroup_id int
SET @old_imgroup_id = 0
BEGIN TRAN
	if EXISTS(SELECT * FROM [USER] WHERE [user_id] = @user_id)
	begin
		SELECT @old_imgroup_id = imgroup_id FROM [User] WHERE [user_id] = @user_id
		UPDATE [USER]
		  SET first_name=@first_name, last_name=@last_name, email=@email
		  WHERE [user_id] = @user_id
		IF @@error != 0
			GOTO err
		IF @imgroup_id > 0
		BEGIN
			UPDATE [USER] SET imgroup_id = @imgroup_id, is_active = @is_active WHERE [user_id] = @user_id
			IF @@error != 0
				GOTO err
		END
		IF @password != ''
		BEGIN
			UPDATE [USER] SET [password] = @password WHERE [user_id] = @user_id
			IF @@error != 0
				GOTO err
		END
		select @retval = @user_id
		IF @old_imgroup_id = @imgroup_id OR @is_active = 0 OR @imgroup_id <= 0
			SET @RECREATE = 0
	end
	else begin
		INSERT INTO [USER] (first_name, last_name, login, [password], imgroup_id, email, is_active)
		  VALUES(@first_name, @last_name, @login, @password, @imgroup_id, @email, @is_active)
		IF @@error != 0
			GOTO err
		select @retval = @@identity
	end
	IF @RECREATE = 1 BEGIN
		DELETE FROM Contact_List WHERE [User_Id] = @retval AND List_Type = 1 AND Has_Authorization = 0
		IF @@error != 0
			GOTO err
		INSERT INTO Contact_List
			SELECT @retval AS [user_id], [user_id] AS cont_user_id, 1 AS list_type, 0 AS Has_Authorization
			FROM [User]
			WHERE (imgroup_id = @imgroup_id OR imgroup_id IN (SELECT dep_imgroup_id FROM Dependences WHERE imgroup_id = @imgroup_id))
				AND [user_id] <> @retval
				AND [user_id] NOT IN (SELECT cont_user_id FROM Contact_List WHERE [user_id] = @retval AND List_Type = 1 )
		IF @@error != 0
			GOTO err
		DELETE FROM Contact_List
		 WHERE cont_user_id = @retval  AND List_Type = 1 AND Has_Authorization = 0 AND
			[user_id] NOT IN (SELECT [user_id] FROM [User] WHERE imgroup_id = @imgroup_id OR imgroup_id IN
					(SELECT imgroup_id FROM Dependences WHERE Dep_imgroup_id = @imgroup_id))
		IF @@error != 0
			GOTO err
		INSERT INTO Contact_List
			SELECT [user_id], @retval AS cont_user_id, 1 AS list_type, 0 AS Has_Authorization
			FROM [User]
			WHERE (imgroup_id = @imgroup_id OR imgroup_id IN (SELECT imgroup_id FROM Dependences WHERE Dep_imgroup_id = @imgroup_id))
				AND [user_id] <> @retval
				AND [user_id] NOT IN (SELECT [user_id] FROM Contact_List WHERE cont_user_id = @retval AND List_Type = 1 )
		IF @@error != 0
			GOTO err
	END
COMMIT TRAN
SET NOCOUNT OFF
RETURN
err:
	ROLLBACK TRAN
	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_ChangeDomain
	@company_id int,
	@NewDomain nvarchar(255),
	@retval int output
AS
BEGIN TRANSACTION
	declare @ind as int
	SET @ind = (SELECT charindex('/', reverse(app_id)) FROM [COMPANIES] WHERE company_id=@company_id)
	declare @Old_App_id as varchar(100)
	SET @Old_App_id=(SELECT reverse(substring(reverse(app_id), @ind+1, len(app_id)-@ind)) FROM [COMPANIES] WHERE company_id=@company_id)
	UPDATE [COMPANIES] SET domain=@NewDomain, app_id=@Old_app_id+'/'+replace(@NewDomain, '.', '_') WHERE company_id = @company_id
	IF @@error!=0 GOTO err
	COMMIT TRAN
	SET @retval = 1
	RETURN
err:
	ROLLBACK TRAN
	SET @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_CHECK_RIGHTS_CHAT
	@user_id as int,
	@cuid as char(36),
	@retval as int output
AS
IF EXISTS(SELECT * FROM Chat_Users WHERE [user_id] = @user_id AND chat_id IN (SELECT chat_id FROM Chats WHERE cuid = @cuid))
	SET @retval = 1
ELSE
	SET @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_CHECK_RIGHTS_USER
	@user_id as int,
	@check_user_id as int,
	@retval as int output
AS
IF EXISTS (SELECT * FROM [USER] WHERE [user_id] = @user_id
		AND imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1 AND company_id IN (SELECT company_id FROM COMPANIES WHERE is_global=1)))
	SET @retval = 1
ELSE BEGIN
	DECLARE @user_company_id int
	SELECT @user_company_id = company_id
	  FROM IMGROUPS
	  WHERE imgroup_id IN (SELECT imgroup_id FROM [User] WHERE [user_id] = @user_id)
	IF EXISTS(SELECT * FROM [User] WHERE [user_id] = @check_user_id
		AND imgroup_id IN (SELECT imgroup_id  FROM IMGROUPS WHERE company_id = @user_company_id
					OR (is_global = 1 AND company_id IN (SELECT company_id FROM COMPANIES WHERE is_global=1))))
		SET @retval = 1
	ELSE
		SET @retval = 0
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_CLONE_IMGROUP_LOGO
	@FROM_IMGROUP_ID as int,
	@TO_IMGROUP_ID as int
as
UPDATE IMGROUPS
  SET client_logo = F.client_logo
  FROM IMGROUPS T, (SELECT client_logo FROM IMGROUPS  WHERE imgroup_id = @FROM_IMGROUP_ID) F
  WHERE T.imgroup_id = @TO_IMGROUP_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_COMPANY_ADD
       @company_id as int ,
       @company_name as nvarchar(100) ,
       @appid as nvarchar(255) ,
       @database as nvarchar(100) ,
       @domain as nvarchar(255) ,
       @description as nvarchar(100) ,
       @contact_name as nvarchar(100) ,
       @contact_phone as nvarchar(100) ,
       @contact_email as nvarchar(100) ,
       @product_name as nvarchar(50) ,
       @max_users as int,
       @max_external_users as int,
       @is_global as bit,
       @company_type as tinyint,
       @start_date as datetime = null,
       @end_date as datetime = null,
       @rate_per_user as int,
       @is_active as bit,
       @max_disk_space as int,
       @useIM as bit,
	@FirstDayOfWeek tinyint,
       @retval int output as
if EXISTS(SELECT company_id FROM COMPANIES WHERE company_id = @company_id)
begin
	UPDATE COMPANIES
 	  SET company_name=@company_name,  domain=@domain, [description]=@description,
		contact_name=@contact_name, contact_phone=@contact_phone, contact_email=@contact_email,
		product_name=@product_name, max_users=@max_users, max_external_users = @max_external_users, is_global=@is_global,
		company_type=@company_type, start_date = @start_date, end_date=@end_date, rate_per_user=@rate_per_user, is_active=@is_active,
		max_disk_space = @max_disk_space, use_im=@useIM, FirstDayOfWeek = @FirstDayOfWeek
	  WHERE company_id = @company_id
	select @retval = @company_id
end else begin
	INSERT INTO COMPANIES (company_name, [db_name], app_id, domain, [description], contact_name, contact_phone, contact_email, product_name,max_users,
					max_external_users, is_global, company_type, start_date, end_date, rate_per_user, is_active, max_disk_space, use_im, FirstDayOfWeek)
	  VALUES(@company_name, @database, @appid, @domain, @description, @contact_name, @contact_phone, @contact_email, @product_name, @max_users,
					@max_external_users, @is_global, @company_type, @start_date, @end_date, @rate_per_user, @is_active, @max_disk_space, @useIM, @FirstDayOfWeek)
	select @retval = @@identity
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DEACTIVATE_USER
	@user_id as int
AS
UPDATE [USER] SET is_active = 0  WHERE [user_id] = @user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DELETE_DEPENDENCES
	@IMGROUP_ID as int,
	@DEP_IMGROUP_ID as int
as
BEGIN TRAN
	DELETE FROM DEPENDENCES  WHERE IMGROUP_ID = @IMGROUP_ID AND DEP_IMGROUP_ID = @DEP_IMGROUP_ID
	IF @@error != 0
		GOTO err
	DELETE FROM Contact_List
	 WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id = @IMGROUP_ID)
		AND cont_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id = @DEP_IMGROUP_ID)
		AND list_type = 1 AND has_authorization = 0
	IF @@error != 0
		GOTO err
COMMIT TRAN
RETURN
err:
	ROLLBACK TRAN
	RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DELETE_IMGROUP
	@IMGROUP_ID as int
as
IF NOT EXISTS (SELECT * FROM IMGROUPS  WHERE IMGROUP_ID = @IMGROUP_ID AND IS_DEFAULT = 1)
BEGIN
BEGIN TRAN
	DELETE FROM DEPENDENCES  WHERE IMGROUP_ID = @IMGROUP_ID OR DEP_IMGROUP_ID = @IMGROUP_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	DELETE FROM IMGROUPS  WHERE IMGROUP_ID = @IMGROUP_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
COMMIT TRAN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DELETE_USER
	@USER_ID as int
as
BEGIN TRAN
	DELETE FROM SESSIONS  WHERE [user_id] = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM PORTAL_SESSIONS  WHERE [user_id] = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM USER_MESS  WHERE from_user_id = @USER_ID OR to_user_id = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM [FILE] WHERE from_user_id = @USER_ID OR to_user_id = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM AUTH_LIST  WHERE from_user_id = @USER_ID OR to_user_id = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM CONTACT_LIST  WHERE [user_id] = @USER_ID OR cont_user_id = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM CHAT_MESS  WHERE [user_id] = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM CHAT_USERS  WHERE [user_id] = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM CHATS  WHERE owner_id = @USER_ID
	IF @@error != 0
		GOTO err
	DELETE FROM [USER ] WHERE [user_id] = @USER_ID
	IF @@error != 0
		GOTO err
COMMIT TRAN
RETURN
err:
	ROLLBACK TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DL_GET_LATEST_VERSION
	@ProductGuid as char(36)
AS
SELECT V.build, V.date
  FROM DL_Versions AS V, DL_Products AS P
  WHERE P.ProductGuid = @ProductGuid AND V.ProductId = P.ProductId
  ORDER BY V.date DESC, V.build DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DL_OsGetList
AS
SELECT DISTINCT os FROM DL_PRODUCTS ORDER BY OS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_DL_ProductGetList
AS
SELECT * FROM DL_PRODUCTS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_EmptyTables
	@company_id int,
	@delete_trial_requests bit = 0,
	@delete_company bit = 0
AS
SET NOCOUNT OFF
DELETE FROM [ACTIVE_USER] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS  WHERE company_id = @company_id))
DELETE FROM [AUTH_LIST] WHERE [from_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [AUTH_LIST] WHERE [to_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CHAT_MESS] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CHAT_USERS] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CHAT_USERS] WHERE [from_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CHATS] WHERE [owner_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CONTACT_LIST] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [CONTACT_LIST] WHERE [cont_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [DEPENDENCES] WHERE [imgroup_id] IN (SELECT [imgroup_id] FROM IMGROUPS WHERE company_id = @company_id)
DELETE FROM [DEPENDENCES] WHERE [dep_imgroup_id] IN (SELECT [imgroup_id] FROM IMGROUPS WHERE company_id = @company_id)
DELETE FROM [BINARY_DATA] WHERE [FID] IN
		(SELECT [file_id] FROM [FILE] WHERE from_user_id IN
			(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
				(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id)))
DELETE FROM [BINARY_DATA] WHERE [FID] IN
		(SELECT [file_id] FROM [FILE] WHERE to_user_id IN
			(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
				(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id)))
DELETE FROM [FILE] WHERE [from_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [FILE] WHERE [to_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS  WHERE company_id = @company_id))
DELETE FROM [SESSIONS] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [PORTAL_SESSIONS] WHERE [user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [USER_MESS] WHERE [from_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [USER_MESS] WHERE [to_user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
DELETE FROM [USER] WHERE [imgroup_id] IN (SELECT [imgroup_id] FROM IMGROUPS WHERE company_id = @company_id)
DELETE FROM [IMGROUPS] WHERE company_id = @company_id
DELETE FROM [TRIAL_NOTIFICATIONS] WHERE company_id = @company_id
if @delete_trial_requests = 1
	DELETE FROM [TRIAL_REQUESTS] WHERE CompanyID=@company_id
ELSE
	UPDATE TRIAL_REQUESTS SET IsDeleted=1 WHERE CompanyID=@company_id
IF @delete_company = 1
	DELETE FROM [COMPANIES] WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_FAILED_TRIAL_REQUEST_CREATE
	@CompanyName as nvarchar(100),
	@SizeOfGroup as nvarchar(50),
	@Description as nvarchar(100),
	@Domain as nvarchar(255),
	@FirstName as nvarchar(100),
	@LastName as nvarchar(100),
	@EMail as nvarchar(100),
	@Phone as nvarchar(50),
	@Country as nvarchar(100),
	@TimeZone as nvarchar(100),
	@Login as nvarchar(50),
	@Password as nvarchar(50),
	@ResellerGuid as uniqueidentifier,
	@ErrorCode as int,
	@retval as int output
AS
DECLARE @ResellerId int
SELECT @ResellerId = ResellerId FROM TRIAL_RESELLERS WHERE Guid = @ResellerGuid
INSERT INTO TRIAL_REQUESTS_FAILED (ErrorCode, CompanyName, SizeOfGroup, Description, Domain, FirstName, LastName, EMail, Phone, Country, TimeZone, Login, Password, ResellerId)
  VALUES (@ErrorCode, @CompanyName, @SizeOfGroup, @Description, @Domain, @FirstName, @LastName, @EMail, @Phone, @Country, @TimeZone, @Login, @Password, @ResellerId)
SET @retval = @@identity
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_FAILED_TRIAL_REQUEST_DELETE
	@RequestID as int
AS
DELETE FROM TRIAL_REQUESTS_FAILED
WHERE RequestID=@RequestID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_FAILED_TRIAL_REQUEST_GET
	@RequestID as int
AS
SELECT RequestID,ErrorCode, CompanyName, SizeOfGroup, [Description], Domain, FirstName, LastName, Email, Phone, Country, TimeZone, Login, Password, R.ResellerId, RS.Title AS ResellerTitle
  FROM TRIAL_REQUESTS_FAILED R
  LEFT JOIN TRIAL_RESELLERS RS ON (R.ResellerId = RS.ResellerId)
  WHERE RequestID = @RequestID OR @RequestID = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_BINARY_CLIENT_LOGO
	@imgroup_id as int
as
SELECT client_logo FROM IMGROUPS WHERE imgroup_id = @imgroup_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_BINARY_COMPANY_LOGO
	@company_id as int
as
SELECT company_logo FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_BINARY_HOMEPAGE_LOGO
	@company_id as int
as
SELECT hp_image AS homepage_logo FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_BINARY_STUB_ICON
	@STUB_ID AS int
as
SELECT icon FROM Stubs WHERE stub_id = @STUB_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_BINARY_USER_PHOTO
       @user_id as int as
SELECT picture FROM USER_DETAILS  WHERE [user_id] = @user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_CHAT_DETAILS
	@CHAT_ID as int
as
DECLARE @n as int
SELECT @n = COUNT(*) FROM Chat_Mess WHERE chat_id = @CHAT_ID
SELECT C.chat_id, C.[name], C.[desc], C.begin_time, owner_id, U.first_name, U.last_name, @n as mess_count
  FROM CHATS  C JOIN [USER] U ON (C.owner_id = U.[user_id])
  WHERE C.chat_id = @CHAT_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_CHAT_ID_BY_CUID
	@cuid as char(36),
	@retval as int output
AS
SELECT @retval = chat_id FROM Chats WHERE cuid = @cuid
IF @@ROWCOUNT = 0
	SET @retval  = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_CHAT_USERS
	@CHAT_ID as int
as
SELECT C.[user_id], C.accepted, C.exited, C.user_status, U.first_name, U.last_name
  FROM CHAT_USERS C
	JOIN [USER] U ON (C.[USER_ID] = U.[USER_ID])
WHERE C.CHAT_ID = @CHAT_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_CHATS
	@USER_ID as int,
	@COMPANY_ID as int
as
SELECT chat_id, [name]
  FROM CHATS
  WHERE (@USER_ID = 0 OR chat_id IN (SELECT chat_id FROM Chat_Users WHERE [user_id] = @USER_ID)) AND
	owner_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGroups WHERE COMPANY_ID = @COMPANY_ID))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_COMPANY
	@company_type as int,
	@company_id as int
as
SELECT company_id, company_name, domain, [db_name], app_id, [description], contact_name, contact_phone, contact_email, support_name, support_email, product_name,
	theme, use_im, title1, title2, text1, text2, max_users, max_external_users, is_global, creation_date, company_type, start_date, end_date, rate_per_user, is_active, max_disk_space,
	enable_alerts, show_admin_wizard, use_ssl, firstdayofweek
  FROM Companies
  WHERE (company_id = @company_id OR @company_id = 0) AND (company_type=@company_type OR @company_type=0)
order by company_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_COMPANY_BY_APP_ID
	@app_id as nvarchar(100)
AS
SELECT company_id, use_im, is_global, domain, enable_alerts, company_type
  FROM Companies WHERE app_id = @app_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_COMPANY_BY_DOMAIN
	@domain as nvarchar(255)
as
SELECT company_id, company_name, [description], app_id, domain, contact_name, contact_phone, contact_email, support_name, support_email, product_name, is_active
  FROM Companies WHERE domain = @domain
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].ASP_GET_COMPANY_BY_KEYWORD
	@Keyword as nvarchar(100)
AS
	SET @Keyword = '%' + @Keyword + '%'
	SELECT company_id, company_name, domain, [db_name], [description], contact_name, contact_phone, contact_email, support_name, support_email, product_name,
	theme, use_im, title1, title2, text1, text2, max_users, max_external_users, is_global, creation_date, company_type, start_date, end_date, rate_per_user, is_active, max_disk_space,
	enable_alerts, show_admin_wizard, use_ssl, firstdayofweek
	FROM Companies
	WHERE company_name LIKE @Keyword or domain LIKE @Keyword or contact_name LIKE @Keyword or contact_email LIKE @Keyword
	or contact_phone LIKE @Keyword or [description] LIKE @Keyword
order by company_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_COMPANY_BY_USER
	@user_id  as int,
	@retval as int output
AS
SELECT @retval = company_id
  FROM IMGROUPS
  WHERE imgroup_id IN (SELECT imgroup_id FROM [User] WHERE [user_id] = @user_id)
IF @@rowcount = 0
	SET @retval = -1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_DATA_SIZE
	@company_id as int,
	@retval bigint output
as
DECLARE @FileSize bigint
DECLARE @MessSize bigint
SELECT @FileSize = SUM(file_size) FROM
  (SELECT DISTINCT [file_id], file_size
    FROM [FILE]
    WHERE [file_id] IN (SELECT FID FROM BINARY_DATA)
	AND from_user_id IN
		(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
  ) A
IF @FileSize IS NULL
	SET @FileSize = 0
SELECT @MessSize = SUM(DATALENGTH(mess_text))
  FROM USER_MESS
  WHERE from_user_id IN
	(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
	(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
IF @MessSize IS NULL
	SET @MessSize = 0
SET @retval = @FileSize + @MessSize
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_DEFAULT_IMGROUP
	@company_id int,
	@retval int output
AS
SELECT @retval = imgroup_id FROM IMGROUPS WHERE is_default = 1 AND company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_FirstDayOfWeek
	@company_id as int
as
select firstdayofweek
  FROM Companies
  WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_GLOBAL_COMPANY
as
SELECT company_id, company_name, [description], contact_name, contact_phone, contact_email, support_name, support_email, product_name
  FROM Companies
  WHERE is_global = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUP_BY_USER
       @USER_ID as int,
       @retval int output as
SET @retval = 0
IF EXISTS(SELECT [user_id] FROM [USER] WHERE [user_id] = @USER_ID)
        SELECT @retval = imgroup_id  FROM [USER]  WHERE [user_id] = @USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUPS
	@imgroup_id as int,
	@company_id as int,
	@include_internal as bit
as
SELECT imgroup_id AS IMGroupId, imgroup_name AS IMGroupName, color, logo_version, is_global, is_partner
  FROM IMGROUPS
  WHERE (imgroup_id = @imgroup_id or @imgroup_id = 0)
	AND company_id = @company_id
	AND (is_internal = 0 OR is_internal = @include_internal)
  ORDER BY imgroup_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUPS_BY_IMGROUPS
	@old_imgroup_id int,
	@imgroup_id int
AS
IF @old_imgroup_id = 0 BEGIN
	SELECT @imgroup_id AS imgroup_id
		UNION
	SELECT imgroup_id FROM Dependences
	 WHERE dep_imgroup_id = @imgroup_id
END
ELSE BEGIN
	SELECT @old_imgroup_id AS imgroup_id
		UNION
	SELECT @imgroup_id AS imgroup_id
		UNION
	SELECT imgroup_id FROM Dependences WHERE dep_imgroup_id = @old_imgroup_id
		UNION
	SELECT imgroup_id FROM Dependences WHERE dep_imgroup_id = @imgroup_id
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUPS_CAN_SEE_YOU
	@IMGROUP_ID as int,
	@COMPANY_ID as int
as
SELECT imgroup_id AS IMGroupId, imgroup_name AS IMGroupName
  FROM IMGROUPS
  WHERE imgroup_id IN (SELECT imgroup_id FROM DEPENDENCES WHERE dep_imgroup_id = @IMGROUP_ID)
  AND company_id = @COMPANY_ID
  ORDER BY imgroup_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUPS_WITHOUT_PARTNERS
	@company_id as int
as
SELECT imgroup_id AS IMGroupId, imgroup_name AS IMGroupName, color, logo_version, is_global, is_partner
  FROM IMGROUPS
  WHERE company_id = @company_id
	AND is_internal = 0
	AND is_partner = 0
  ORDER BY imgroup_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_IMGROUPS_YOU_CAN_SEE
	@IMGROUP_ID as int,
	@COMPANY_ID as int,
	@include_self as bit
as
SELECT imgroup_id AS IMGroupId, imgroup_name AS IMGroupName
  FROM IMGROUPS
  WHERE (imgroup_id IN (SELECT dep_imgroup_id FROM DEPENDENCES WHERE imgroup_id = @IMGROUP_ID) OR (imgroup_id = @IMGROUP_ID AND @include_self = 1))
  AND company_id = @COMPANY_ID
  ORDER BY imgroup_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_LIST_DL_CATEGORIES
	@CategoryId as int
AS
Select * From DL_Categories Where (CategoryId=@CategoryId) OR (@CategoryId=0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_LIST_PRODUCTS_BY_CATEGORY
	@CategoryId as int
AS
SELECT P.ProductID, P.ProductGuid, P.Title AS ProductTitle, P.[Description],
	P.Locale, P.OS, P.[FileName], P.AutoInstall, C.CategoryID,
	C.Title As CategoryTitle, B.Build, B.[Date]
  FROM DL_PRODUCTS AS P, DL_CATEGORIES AS C,DL_PRODUCT_CATEGORIES AS PC,
	(SELECT V.ProductId, A.Build, V.[Date]
	  FROM DL_VERSIONS V JOIN
		(SELECT ProductId, MAX(Build) AS Build
		  FROM DL_VERSIONS
		  GROUP BY ProductId) A ON V.ProductId = A.ProductId) B
WHERE (P.PRODUCTID=PC.PRODUCTID)
	AND(PC.CATEGORYID=@CategoryId)
	AND(PC.CATEGORYID=C.CATEGORYID)
	AND P.ProductId = B.ProductId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_LIST_PRODUCTS_BY_FILTER
	@CategoryId as int,
	@Locale as char(5),
	@OS as nvarchar(100)
AS
SELECT P.ProductID, P.ProductGuid, P.Title AS ProductTitle, P.[Description], P.Locale, P.OS, P.[FileName], P.AutoInstall, C.CategoryID, C.Title AS CategoryTitle, B.Build, B.[Date]
  FROM DL_PRODUCTS AS P, DL_CATEGORIES AS C,DL_PRODUCT_CATEGORIES AS PC,
	(SELECT V.ProductId, A.Build, V.[Date]
	  FROM DL_VERSIONS V JOIN
		(SELECT ProductId, MAX(Build) AS Build
		  FROM DL_VERSIONS
		  GROUP BY ProductId) A ON V.ProductId = A.ProductId) B
  WHERE P.PRODUCTID = PC.PRODUCTID AND C.CATEGORYID = PC.CATEGORYID
	AND P.ProductId = B.ProductId
	AND (PC.CATEGORYID = @CategoryId OR @CategoryId = 0)
	AND (P.Locale = @Locale OR @Locale = '0')
	AND (P.OS = @OS OR @OS = '0')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[ASP_GET_LOGO_BY_IMGROUP]
		@imgroup_id as int
 AS
SELECT logo_version, color FROM IMGROUPS WHERE imgroup_id=@imgroup_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_MAX_BUILD
	@ProductGuid as char(36),
	@retval int output
AS
SET @retval = -1
IF EXISTS(SELECT V.Build FROM DL_Versions AS V, DL_Products AS P Where (V.ProductId=P.ProductId)AND(P.ProductGuid=@productguid))
	SELECT @retval = MAX(V.build) FROM DL_Versions AS V, DL_Products AS P Where (V.ProductId=P.ProductId)AND(P.ProductGuid=@ProductGuid)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_MAX_DISK_SPACE
	@company_id as int,
	@retval int output
AS
SELECT @retval = max_disk_space FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_MAX_EXTERNAL_USERS
	@company_id as int,
	@retval int output
AS
SELECT @retval = max_external_users FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_MAX_USERS
	@company_id as int,
	@retval int output
AS
SELECT @retval = max_users FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_PORTAL_DB_BY_APP_ID
	@app_id as nvarchar(100)
AS
SELECT db_name FROM COMPANIES WHERE app_id = @app_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_PORTAL_DB_BY_DOMAIN
	@domain as nvarchar(255)
AS
SELECT db_name FROM COMPANIES WHERE domain = @domain
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_PORTAL_NAME_BY_COMPANY
	@company_id as int
as
SELECT [db_name] FROM COMPANIES WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_PRODUCT_BY_GUID
	@ProductGuid as char(36)
AS
SELECT * FROM DL_PRODUCTS
	WHERE ProductGuid=@ProductGuid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_PRODUCT_BY_ID
	@ProductId as int
AS
SELECT * FROM DL_PRODUCTS
	WHERE PRODUCTID=@ProductId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].ASP_GET_RESSELLER_BY_KEYWORD
	@Keyword as nvarchar(100)
AS
	SET @Keyword = '%' + @Keyword + '%'
	SELECT RS.ResellerId as ResellerId, Title, RS.Guid as Guid, ContactName, ContactEmail, ContactPhone, RS.CommissionPercentage
	FROM TRIAL_RESELLERS RS
	WHERE Title LIKE  @Keyword or ContactName LIKE @Keyword or ContactEmail LIKE @Keyword or ContactPhone LIKE @Keyword
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_STUBS_BY_IMGROUP
	@IMGROUP_ID int
AS
SELECT stub_id, short_name, full_name, tooltip, url, open_window, width, height, CASE WHEN Icon IS NULL THEN 0 ELSE 1 END AS ShowIcon, IsInternal
 FROM Stubs
 WHERE stub_id IN (SELECT stub_id FROM STUB_IMGROUP WHERE IMGROUP_ID = @IMGROUP_ID)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_STUBS_VERSION
	@USER_ID int,
	@retval int output
AS
SELECT @retval = stubs_version FROM [User] WHERE [user_id] = @USER_ID
IF @@rowcount = 0
	SET @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].ASP_GET_TRIAL_BY_KEYWORD
	@Keyword as nvarchar(100)
AS
	SET @Keyword = '%' + @Keyword + '%'
	SELECT RequestID, CompanyName, CompanyID, SizeOfGroup, [Description], Domain, FirstName, LastName, Email, Phone, Country, TimeZone, Login, Password, R.ResellerId, RS.Title AS ResellerTitle, XML, R.GUID, UseIM, IsActive, IsDeleted, CreationDate, Locale
	FROM TRIAL_REQUESTS R
	JOIN TRIAL_RESELLERS RS ON (R.ResellerId = RS.ResellerId)
	WHERE (CompanyName LIKE @Keyword or [Description] LIKE @Keyword
			or Domain LIKE @Keyword or FirstName LIKE @Keyword or LastName LIKE @Keyword or Email LIKE @Keyword or Phone LIKE @Keyword
			) AND IsDeleted = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_USER_STATUS
	@user_id as int,
	@retval as int output
as
SELECT @retval = status FROM ACTIVE_USER WHERE [user_id] = @user_id
IF @@ROWCOUNT = 0
	SET @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_GET_USERS_BY_COMPANY
	@company_id as int
as
SELECT U.user_id  AS UserId, U.first_name+' '+U.last_name AS DisplayName, R.IMGroup_Id AS IMGroupId, R.imgroup_name AS IMGroupName
  FROM [USER] AS U, IMGROUPS AS R
  WHERE (U.imgroup_id = R.imgroup_id)
	AND (R.company_id = @company_id ) AND (U.login!='alert')
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_INCREASE_STUBS_VERSION
	@user_id as int
AS
UPDATE [USER] SET stubs_version = stubs_version + 1  WHERE [user_id] = @user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_LOG_PORTAL_LOGIN
	@USER_ID INT,
	@RETVAL INT OUTPUT
AS
INSERT INTO Portal_Sessions ([user_id], Dt_Begin) VALUES (@USER_ID, DATEDIFF(s, '1970-01-01 00:00', GETDATE()))
SET @retval = @@rowcount
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_RESET_HOMEPAGE_LOGO
	@COMPANY_ID int
AS
UPDATE COMPANIES SET hp_image = null
  WHERE COMPANY_ID = @COMPANY_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SEARCH_CHAT
	@User_id int,
	@Chat_id int,
	@DT_Begin datetime,
	@DT_End datetime,
	@Keyword nvarchar(50),
	@Order int,
	@TimeOffset int,
	@Company_Id int
AS
SET @Keyword = '%' + RTRIM(LTRIM(@Keyword)) + '%'
SELECT U.first_name, U.last_name, mess_text, DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') AS send_time
 FROM Chat_Mess M
	JOIN [User] U ON (M.[User_id] = U.[user_id])
 WHERE (@Chat_id = 0 OR Chat_id = @Chat_id) AND
	DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') > @DT_Begin AND
	DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') < @DT_End AND
	mess_text LIKE @Keyword AND
	U.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_Id = @Company_Id) AND
	(@User_id = 0 OR chat_id IN (SELECT chat_id FROM Chat_Users WHERE [user_id] = @User_id))
 ORDER BY CASE WHEN @Order = 1 THEN -send_time ELSE send_time END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SEARCH_HISTORY
	@CompanyId int,
	@User_id int,
	@Cont_user_id int,
	@DT_Begin datetime,
	@DT_End datetime,
	@Mess_Type int,
	@Keyword nvarchar(50),
	@Order int,
	@TimeOffset int
AS
SET @Keyword = '%' + RTRIM(LTRIM(@Keyword)) + '%'
IF @Mess_Type = 1 BEGIN
	SELECT U1.first_name AS FromFirst, U1.last_name AS FromLast, U1.[User_ID] AS FromID, U2.first_name AS ToFirst,  U2.last_name AS ToLast, U2.[User_ID] AS ToID, mess_text, DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') AS send_time
	 FROM User_Mess M, [User] U1, [User] U2
	 WHERE DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') > @DT_Begin AND DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') < @DT_End AND
		M.From_User_id = U1.[user_id] AND M.To_User_id = U2.[user_id] AND
		(
			 (@User_id = 0 OR M.To_User_Id = @User_id)
			AND
			 (@Cont_user_id = 0 OR M.From_User_id = @Cont_user_id)
		OR
			 (@User_id = 0 OR M.From_User_id = @User_id)
			AND
			 (@Cont_user_id = 0 OR M.To_User_Id = @Cont_user_id)
		) AND
		(mess_text LIKE @Keyword) AND (U1.[login] != 'alert') AND (U2.[login] != 'alert')
		AND (U1.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
		AND (U2.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
	 ORDER BY CASE WHEN @Order = 1 THEN -send_time ELSE send_time END
END
ELSE IF @Mess_Type = 2 BEGIN
	SELECT U1.first_name AS FromFirst, U1.last_name AS FromLast, U1.[User_ID] AS FromID, U2.first_name AS ToFirst,  U2.last_name AS ToLast, U2.[User_ID] AS ToID, mess_text, DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') AS send_time
	 FROM User_Mess M, [User] U1, [User] U2
	 WHERE DATEADD(s, Send_Time, '1970-01-01 00:00') > @DT_Begin AND DATEADD(s, Send_Time, '1970-01-01 00:00') < @DT_End AND
		M.From_User_id = U1.[user_id] AND M.To_User_id = U2.[user_id] AND
		(@User_id = 0 OR M.To_User_Id = @User_id) AND
		(@Cont_user_id = 0 OR M.From_User_id = @Cont_user_id) AND
		(mess_text LIKE @Keyword) AND (U1.[login] != 'alert') AND (U2.[login] != 'alert')
		AND (U1.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
		AND (U2.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
	 ORDER BY CASE WHEN @Order = 1 THEN -send_time ELSE send_time END
END
ELSE IF @Mess_Type = 3 BEGIN
	SELECT U1.first_name AS FromFirst, U1.last_name AS FromLast, U1.[User_ID] AS FromID, U2.first_name AS ToFirst,  U2.last_name AS ToLast,  U2.[User_ID] AS ToID, mess_text, DATEADD(s, Send_Time - @TimeOffset, '1970-01-01 00:00') AS send_time
	 FROM User_Mess M, [User] U1, [User] U2
	 WHERE DATEADD(s, Send_Time, '1970-01-01 00:00') > @DT_Begin AND DATEADD(s, Send_Time, '1970-01-01 00:00') < @DT_End AND
		M.From_User_id = U1.[user_id] AND M.To_User_id = U2.[user_id] AND
		(@User_id = 0 OR M.From_User_id = @User_id) AND
		(@Cont_user_id = 0 OR M.To_User_Id = @Cont_user_id) AND
		(mess_text LIKE @Keyword) AND (U1.[login] != 'alert') AND (U2.[login] != 'alert')
		AND (U1.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
		AND (U2.[imgroup_id] IN (SELECT imgroup_id FROM IMGroups WHERE Company_id = @CompanyId))
	 ORDER BY CASE WHEN @Order = 1 THEN -send_time ELSE send_time END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SET_ALERTS_ENABLING
	@company_id int,
	@enable_alerts bit
AS
UPDATE COMPANIES SET enable_alerts = @enable_alerts WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SET_DEFAULT_IMGROUP
	@imgroup_id int
AS
DECLARE @company_id int
SELECT @company_id = company_id
  FROM IMGROUPS
  WHERE imgroup_id = @imgroup_id
BEGIN TRAN
	UPDATE IMGROUPS SET is_default = 0 WHERE company_id = @company_id
	IF @@ERROR <> 0
		GOTO err
	UPDATE IMGROUPS SET is_default = 1 WHERE imgroup_id = @imgroup_id
	IF @@ERROR <> 0
		GOTO err
COMMIT TRAN
RETURN
err:
	ROLLBACK TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SET_FirstDayOfWeek
	@company_id as int,
	@firstdayofweek as tinyint
as
update Companies
	set firstdayofweek = @firstdayofweek
	WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_SET_SHOW_ADMIN_WIZARD
	@company_id int,
	@show_admin_wizard bit
AS
UPDATE COMPANIES SET show_admin_wizard = @show_admin_wizard WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_COMPANY_DEACTIVATE
	@CompanyID as int
AS
UPDATE COMPANIES SET is_active = 0 WHERE company_id = @CompanyID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_COMPANY_GET_BY_CATEGORY
	@Category as int,
	@Date as datetime
AS
IF @Category = 1
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 0 AND end_date < @Date
IF @Category = 2
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 1 AND end_date < @Date
IF @Category = 3
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 1 AND end_date < @Date
IF @Category = 4
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 1 AND end_date <= @Date
IF @Category = 5
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 1 AND creation_date < @Date
IF @Category = 6
	SELECT company_id FROM Companies WHERE company_type = 2 AND is_active = 1 AND creation_date < @Date
		AND company_id NOT IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
			(SELECT imgroup_id FROM [USER] WHERE [user_id] IN
				(SELECT [user_id] FROM SESSIONS)
			)
		)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_NOTIFICATION_GET
	@company_id int,
	@notif_id int,
	@retval int output
as
SET @retval = 0
IF EXISTS(SELECT company_id FROM TRIAL_NOTIFICATIONS WHERE company_id = @company_id)
	SELECT @retval = CASE @notif_id
		WHEN 1 THEN after_week
		WHEN 2 THEN before_week
		WHEN 3 THEN before_day
		WHEN 4 THEN after_day_im
		END
	FROM TRIAL_NOTIFICATIONS
	WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_NOTIFICATION_UPDATE
	@company_id int,
	@notif_id int,
	@value bit
as
IF NOT EXISTS(SELECT company_id FROM TRIAL_NOTIFICATIONS WHERE company_id = @company_id)
	INSERT INTO TRIAL_NOTIFICATIONS (company_id) VALUES (@company_id)
IF @notif_id = 1 UPDATE TRIAL_NOTIFICATIONS SET after_week = @value WHERE company_id = @company_id
IF @notif_id = 2 UPDATE TRIAL_NOTIFICATIONS SET before_week = @value WHERE company_id = @company_id
IF @notif_id = 3 UPDATE TRIAL_NOTIFICATIONS SET before_day = @value WHERE company_id = @company_id
IF @notif_id = 4 UPDATE TRIAL_NOTIFICATIONS SET after_day_im = @value WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_CREATE
	@CompanyName as nvarchar(100),
	@SizeOfGroup as nvarchar(50),
	@Description as nvarchar(100),
	@Domain as nvarchar(255),
	@FirstName as nvarchar(100),
	@LastName as nvarchar(100),
	@EMail as nvarchar(100),
	@Phone as nvarchar(50),
	@Country as nvarchar(100),
	@TimeZone as nvarchar(100),
	@Login as nvarchar(50),
	@Password as nvarchar(50),
	@ResellerGuid as uniqueidentifier,
	@XML as nvarchar(4000),
	@UseIM as bit,
	@Locale as nvarchar(10),
	@retval as int output
AS
DECLARE @ResellerId int
SELECT @ResellerId = ResellerId FROM TRIAL_RESELLERS WHERE Guid = @ResellerGuid
if @ResellerId is null
	SELECT @ResellerId = ResellerId FROM TRIAL_RESELLERS WHERE IsDefault = 1
INSERT INTO TRIAL_REQUESTS (CompanyName, SizeOfGroup, Description, Domain, FirstName, LastName, EMail, Phone, Country, TimeZone, Login, Password, ResellerId, XML, UseIM, Locale)
  VALUES (@CompanyName, @SizeOfGroup, @Description, @Domain, @FirstName, @LastName, @EMail, @Phone, @Country, @TimeZone, @Login, @Password, @ResellerId, @XML, @UseIM, @Locale)
SET @retval = @@identity
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_DELETE
	@RequestID as int
AS
UPDATE TRIAL_REQUESTS SET IsDeleted = 1 WHERE RequestID = @RequestID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_DELETE_BY_COMPANY
	@CompanyID as int
AS
UPDATE TRIAL_REQUESTS SET IsDeleted = 1 WHERE CompanyID = @CompanyID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_GET
	@RequestID as int,
	@IncludeInactive as bit
AS
SELECT RequestID, CompanyName, CompanyID, SizeOfGroup, [Description], Domain, FirstName, LastName, Email, Phone, Country, TimeZone, Login, Password, R.ResellerId, RS.Title AS ResellerTitle, XML, R.GUID, UseIM, IsActive, IsDeleted, CreationDate, Locale
  FROM TRIAL_REQUESTS R
  JOIN TRIAL_RESELLERS RS ON (R.ResellerId = RS.ResellerId)
  WHERE (@IncludeInactive = 1 OR IsActive = 1)  AND (RequestID = @RequestID OR @RequestID = 0) AND IsDeleted = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_GET_BY_DOMAIN
	@Domain as nvarchar(100)
AS
SELECT RequestID
  FROM TRIAL_REQUESTS WHERE Domain = @Domain
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_GET_BY_GUID
	@guid as uniqueidentifier,
	@retval as int output
AS
SELECT @retval = RequestID
  FROM TRIAL_REQUESTS WHERE GUID = @guid AND IsDeleted = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_GET_BY_USER
	@EMail as nvarchar(100)
AS
SELECT RequestID
  FROM TRIAL_REQUESTS WHERE EMail = @EMail
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_SET_ACTIVE
	@RequestId as int,
	@CompanyId as int,
	@IsActive as bit
AS
UPDATE TRIAL_REQUESTS SET IsActive = @IsActive, CompanyID = @CompanyId
WHERE RequestId = @RequestId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_REQUEST_UPDATE
	@RequestID as int,
	@CompanyName as nvarchar(100),
	@SizeOfGroup as nvarchar(50),
	@Description as nvarchar(100),
	@Domain as nvarchar(255),
	@FirstName as nvarchar(100),
	@LastName as nvarchar(100),
	@EMail as nvarchar(100),
	@Phone as nvarchar(50),
	@Country as nvarchar(100),
	@TimeZone as nvarchar(100),
	@Login as nvarchar(50),
	@Password as nvarchar(50),
	@UseIM as bit,
	@Locale as nvarchar(10)
AS
UPDATE TRIAL_REQUESTS SET CompanyName = @CompanyName, SizeOfGroup = @SizeOfGroup, Description = @Description, Domain = @Domain,
  FirstName = @FirstName, LastName = @LastName, EMail = @EMail, Phone = @Phone, Country = @Country, TimeZone = @TimeZone,
  Login = @Login, Password = @Password, UseIM = @UseIM, Locale = @Locale
WHERE RequestID = @RequestID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_RESELLER_CREATE_UPDATE
	@ResellerId as int,
	@Title as nvarchar(255),
	@ContactName as nvarchar(255),
	@ContactEmail as nvarchar(100),
	@ContactPhone as nvarchar(50),
	@CommPerc as int,
	@retval as int output
AS
IF @ResellerId = 0
BEGIN
	INSERT INTO TRIAL_RESELLERS (Title, ContactName, ContactEmail, ContactPhone, CommissionPercentage)
	  VALUES (@Title, @ContactName, @ContactEmail, @ContactPhone, @CommPerc)
	SET @retval = @@identity
END
ELSE
BEGIN
	UPDATE TRIAL_RESELLERS SET Title = @Title, ContactName = @ContactName, ContactEmail = @ContactEmail, ContactPhone = @ContactPhone, CommissionPercentage = @CommPerc
	  WHERE ResellerId = @ResellerId
	SET @retval = @ResellerId
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_RESELLER_GET
	@ResellerId as int
AS
SELECT RS.ResellerId as ResellerId, Title, RS.Guid as Guid, ContactName, ContactEmail, ContactPhone, A.counter as TrialsCount, RS.CommissionPercentage,
			(Select Count(C.Company_id) From Companies As C, Trial_Requests AS TRQ
			Where (C.Company_type = 1) AND (C.Company_Id = TRQ.CompanyId) AND (TRQ.ResellerId = RS.ResellerId)) As BillableCount, IsDefault
  FROM TRIAL_RESELLERS RS
   JOIN (SELECT RS.ResellerId as ResellerId, Count(RQ.ResellerId) as counter
             FROM TRIAL_RESELLERS RS
             LEFT JOIN TRIAL_REQUESTS RQ ON (RS.ResellerId = RQ.ResellerId AND RQ.IsActive=1)
             GROUP BY (RS.ResellerId)) A ON (RS.ResellerId = A.ResellerId)
  WHERE RS.ResellerId = @ResellerId OR @ResellerId = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_RESELLER_GET_BY_COMPANY
	@CompanyId as int
AS
SELECT TR.ResellerId, TR.Title, TR.Guid, TR.ContactName, TR.ContactEmail, TR.ContactPhone, TR.CommissionPercentage
  FROM TRIAL_RESELLERS AS TR, TRIAL_REQUESTS AS TRQ
  WHERE (TR.ResellerId = TRQ.ResellerId)AND (TRQ.CompanyId = @CompanyId)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_RESELLER_TOPSTATISTIC_GET
	@ResellerId as int
AS
Select top 20 TRS.ResellerId, TRS.Title, (Select Count(C.Company_id) From Companies As C, Trial_Requests AS TRQ
			Where (C.Company_type = 2) AND (C.Company_Id = TRQ.CompanyId) AND (TRQ.ResellerId = TRS.ResellerId)) AS TrialsCount,
		  (Select Count(C.Company_id) From Companies As C, Trial_Requests AS TRQ
			Where (C.Company_type = 1) AND (C.Company_Id = TRQ.CompanyId) AND (TRQ.ResellerId = TRS.ResellerId)) As BillableCount
	From Trial_Resellers AS TRS Where (ResellerId = @ResellerId)OR(@ResellerId=0)
	ORDER BY BillableCount DESC, TrialsCount DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_SETTINGS_GET
	@ProfileID as int
AS
SELECT TOP 1 ProfileID, ProfileName, ManualActivation, MaxHDD, MaxUsers, MaxExternalUsers, TrialPeriod, UseIM, AdminEmail, ParentDomain, IpAddress, ServerLink,
	EmailFrom, EmailTrial, UseAlertService, AlertServiceURL, SMTPServer, ResellerTemplate, ResellerBanner, TrialTopLogo
  FROM TRIAL_PROFILES
  WHERE ProfileID = @ProfileID OR @ProfileID = 0 OR @ProfileID = -1 AND IsActive = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_TRIAL_SETTINGS_UPDATE
	@ProfileID as int,
	@ProfileName as nvarchar(50),
	@ManualActivation as bit,
	@MaxHDD as int,
	@MaxUsers as int,
	@MaxExternalUsers as int,
	@TrialPeriod as int,
	@UseIM as bit,
	@AdminEmail as nvarchar(100),
	@ParentDomain as nvarchar(100),
	@IpAddress as nvarchar(15),
	@ServerLink as nvarchar(100),
	@EmailFrom as nvarchar(100),
	@EmailTrial as nvarchar(100),
	@UseAlertService as bit,
	@AlertServiceURL as nvarchar(255),
	@SMTPServer as nvarchar(255),
	@ResellerTemplate as ntext,
	@ResellerBanner as image,
	@TrialTopLogo as image
AS
IF @ProfileID = -1
	INSERT INTO TRIAL_PROFILES (ProfileName, ManualActivation, MaxHDD, MaxUsers, MaxExternalUsers, TrialPeriod, UseIM, AdminEmail,
	  ParentDomain, IpAddress, ServerLink, EmailFrom, EmailTrial, UseAlertService, AlertServiceURL, SMTPServer,
	  ResellerTemplate, ResellerBanner, TrialTopLogo)
	  VALUES (@ProfileName, @ManualActivation, @MaxHDD, @MaxUsers, @MaxExternalUsers, @TrialPeriod, @UseIM, @AdminEmail,
	  @ParentDomain, @IpAddress, @ServerLink, @EmailFrom, @EmailTrial, @UseAlertService, @AlertServiceURL, @SMTPServer,
	  @ResellerTemplate, @ResellerBanner, @TrialTopLogo)
ELSE
	UPDATE TRIAL_PROFILES SET ProfileName = @ProfileName, ManualActivation = @ManualActivation, MaxHDD = @MaxHDD,
	  MaxUsers = @MaxUsers, MaxExternalUsers = @MaxExternalUsers, TrialPeriod = @TrialPeriod, UseIM = @UseIM, AdminEmail = @AdminEmail, ParentDomain = @ParentDomain,
	  IpAddress = @IpAddress, ServerLink = @ServerLink, EmailFrom = @EmailFrom, EmailTrial = @EmailTrial, UseAlertService = @UseAlertService,
	  AlertServiceURL = @AlertServiceURL, SMTPServer = @SMTPServer, ResellerTemplate = @ResellerTemplate, ResellerBanner = @ResellerBanner,
	  TrialTopLogo = @TrialTopLogo
	  WHERE ProfileID = @ProfileID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_COMPANY
	@company_id as int,
	@company_name as nvarchar(100),
	@description as ntext,
	@contact_name as nvarchar(100) ,
	@contact_phone as nvarchar(100) ,
	@contact_email as nvarchar(100),
	@support_name as nvarchar(100),
	@support_email nvarchar(100),
	@product_name as nvarchar(100),
	@title1 as nvarchar(500),
	@title2 as nvarchar(500),
	@text1 as ntext,
	@text2 as ntext
as
UPDATE COMPANIES SET company_name=@company_name, [description]=@description,
	contact_name=@contact_name, contact_phone=@contact_phone, contact_email=@contact_email,
	support_name=@support_name, support_email=@support_email, product_name=@product_name,
	title1=@title1, title2=@title2, text1=@text1, text2 = @text2
  WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_COMPANY_LOGO
	@COMPANY_ID int,
	@COMPANY_LOGO image
AS
UPDATE COMPANIES SET COMPANY_LOGO = @COMPANY_LOGO
  WHERE COMPANY_ID = @COMPANY_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_COMPANY_SSL
	@company_id as int,
	@use_ssl as bit
as
UPDATE COMPANIES SET use_ssl = @use_ssl
  WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_COMPANY2
	@company_id as int,
	@title1 as nvarchar(500),
	@title2 as nvarchar(500),
	@text1 as ntext,
	@text2 as ntext
as
UPDATE COMPANIES SET title1=@title1, title2=@title2, text1=@text1, text2 = @text2
  WHERE company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_HOMEPAGE_LOGO
	@COMPANY_ID int,
	@HOMEPAGE_LOGO image
AS
UPDATE COMPANIES SET hp_image = @HOMEPAGE_LOGO
  WHERE COMPANY_ID = @COMPANY_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_IMGROUP_LOGO
	@IMGROUP_ID int,
	@IMGROUP_LOGO image
AS
UPDATE IMGROUPS SET CLIENT_LOGO = @IMGROUP_LOGO, logo_version = logo_version + 1
  WHERE IMGROUP_ID = @IMGROUP_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].ASP_UPDATE_USER
	@user_id as int ,
	@first_name as nvarchar(50) ,
	@last_name as nvarchar(50) ,
	@email as nvarchar(250)
AS
UPDATE [USER]
  SET first_name=@first_name, last_name=@last_name, email=@email
  WHERE [user_id] = @user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].Company_SetActive
	@CompanyId INT,
	@IsActive BIT
AS
UPDATE COMPANIES
 SET is_active = @IsActive
 WHERE company_id = @CompanyId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].Company_SetType
	@CompanyId INT,
	@Type TINYINT
AS
UPDATE COMPANIES
 SET company_type = @Type
 WHERE company_id = @CompanyId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].Company_UpdateGeneral
       @CompanyId INT,
       @CompanyName NVARCHAR(100),
       @ProductName NVARCHAR(50),
       @IsGlobal BIT,
       @IsActive BIT,
       @MaxUsers INT,
       @MaxExternalUsers INT,
       @MaxDiskSpace INT,
       @ContactName NVARCHAR(100),
       @ContactPhone NVARCHAR(100),
       @ContactEmail NVARCHAR(100)
AS
UPDATE COMPANIES
 SET company_name = @CompanyName, product_name = @ProductName, is_global = @IsGlobal, is_active = @IsActive,
  max_users = @MaxUsers, max_external_users = @MaxExternalUsers, max_disk_space = @MaxDiskSpace,
  contact_name = @ContactName, contact_phone = @ContactPhone, contact_email = @ContactEmail
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].Company_UpdateRate
       @CompanyId INT,
       @RatePerUser INT
AS
UPDATE COMPANIES
 SET rate_per_user = @RatePerUser
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].Company_UpdateTrialPeriod
       @CompanyId INT,
       @StartDate DATETIME,
       @EndDate DATETIME
AS
UPDATE COMPANIES
 SET start_date = @StartDate, end_date = @EndDate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_CHANGE_CHAT_STATUS
	@CUID char(36),
	@USER_ID INT,
	@USER_STATUS tinyint,
	@TIME int,
	@RETVAL INT OUTPUT
AS
UPDATE CHAT_USERS SET USER_STATUS = @USER_STATUS, [TIME] =  @TIME
  WHERE [USER_ID] = @USER_ID AND CHAT_ID IN
	(SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID)
SET @RETVAL = @@ROWCOUNT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].IBN_CHAT_USER_REPLY
	@CUID char(36),
	@USER_ID INT,
	@ACCEPTED INT
AS
SET NOCOUNT ON
UPDATE CHAT_USERS SET ACCEPTED = @ACCEPTED
  WHERE [USER_ID] = @USER_ID AND CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID)
IF @@ROWCOUNT = 0 BEGIN
	RAISERROR ('The user does not belong to the chat or chat does not exist.', 16, 1)
	RETURN
END
SELECT @CUID as CUID, U.[USER_ID], U.LOGIN, U.FIRST_NAME, U.LAST_NAME, U.EMAIL, 0 AS STATUS, U.IMGROUP_ID, R.IMGROUP_NAME, @ACCEPTED AS ACCEPTED, 0 AS [TIME]
 FROM [USER] U
	JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
 WHERE U.[user_id] = @USER_ID
IF @ACCEPTED = 0
	DELETE FROM CHAT_USERS WHERE [USER_ID] = @USER_ID AND CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID)
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_CREATE_CHAT
	@CUID as char(36) ,
	@NAME as nvarchar(50) ,
	@DESC as nvarchar(1000) ,
	@OWNER_ID as int ,
	@BEGIN_TIME as int
as
SET NOCOUNT ON
DECLARE @CHAT_ID int
DECLARE @EXISTING_OWNER_ID int
DECLARE @EXISTING_NAME nvarchar(50)
DECLARE @EXISTING_DESC nvarchar(1000)
DECLARE @EXISTING_BEGIN_TIME int
SELECT @CHAT_ID=CHAT_ID, @EXISTING_OWNER_ID = OWNER_ID, @EXISTING_NAME = [NAME],  @EXISTING_DESC = [DESC], @EXISTING_BEGIN_TIME = BEGIN_TIME
  FROM CHATS WHERE CUID = @CUID
IF @@ROWCOUNT = 0
BEGIN
	BEGIN TRAN
		INSERT INTO CHATS ([NAME], [DESC], CUID, OWNER_ID, BEGIN_TIME)
		  VALUES(@NAME, @DESC, @CUID, @OWNER_ID, @BEGIN_TIME)
		IF @@error != 0
		BEGIN
			ROLLBACK TRAN
			RETURN
		END
		SELECT @CHAT_ID = @@identity
		INSERT INTO CHAT_USERS (CHAT_ID, [USER_ID], ACCEPTED, EXITED, USER_STATUS, FROM_USER_ID, [TIME])
		  VALUES (@CHAT_ID, @OWNER_ID, 1, 0, 1, @OWNER_ID, @BEGIN_TIME)
		IF @@error != 0
		BEGIN
			ROLLBACK TRAN
			RETURN
		END
	COMMIT TRAN
END ELSE BEGIN
	IF @EXISTING_OWNER_ID <> @OWNER_ID OR @EXISTING_NAME <> @NAME OR @EXISTING_DESC <> @DESC OR @EXISTING_BEGIN_TIME <> @BEGIN_TIME
	BEGIN
		RAISERROR ('Chat exists already .', 16, 1)
		RETURN
	END
END
SELECT CUID, [NAME], [DESC], OWNER_ID, BEGIN_TIME FROM CHATS WHERE CHAT_ID = @CHAT_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_EDIT_CHAT
	@CUID as char(36) ,
	@NAME as nvarchar(50) ,
	@DESC as nvarchar(1000) ,
	@OWNER_ID as int
as
SET NOCOUNT ON
DECLARE @CHAT_ID int
DECLARE @EXISTING_OWNER_ID int
SELECT @CHAT_ID=CHAT_ID, @EXISTING_OWNER_ID = OWNER_ID
  FROM CHATS WHERE CUID = @CUID
IF @@ROWCOUNT = 0
BEGIN
	RAISERROR ('Chat does not exist.', 16, 1)
	RETURN
END
IF @EXISTING_OWNER_ID <> @OWNER_ID
BEGIN
	RAISERROR ('The owner is not the same.', 16, 1)
	RETURN
END
UPDATE CHATS SET [NAME] = @NAME, [DESC] = @DESC WHERE CHAT_ID = @CHAT_ID
SELECT CUID, [NAME], [DESC], OWNER_ID, BEGIN_TIME FROM CHATS WHERE CHAT_ID = @CHAT_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_GET_CHAT_INFO
	@CUID as CHAR(36)
as
SELECT [NAME], [DESC], CUID, OWNER_ID, BEGIN_TIME, END_TIME
  FROM CHATS
  WHERE CUID = @CUID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_GET_CHAT_USER_INFO
	@CUID as char(36),
	@USER_ID as int
as
SELECT @CUID as CUID, C.[USER_ID], C.USER_STATUS as STATUS, U.LOGIN, U.FIRST_NAME, U.LAST_NAME, U.EMAIL, U.IMGROUP_ID, R.IMGROUP_NAME, C.[TIME]
  FROM CHAT_USERS C
	JOIN [USER] U ON (C.[USER_ID] = U.[USER_ID])
	JOIN IMGROUPS R ON (U.IMGROUP_ID = R.IMGROUP_ID)
WHERE C.CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID) AND
	C.[USER_ID] = @USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].IBN_INVITE_CHAT_USER
	@CUID as char(36) ,
	@FROM_USER_ID as int ,
	@TO_USER_ID as int ,
	@INVITE_TEXT as nvarchar(1024)
as
SET NOCOUNT ON
DECLARE @CHAT_ID int
DECLARE @ACCEPTED int
DECLARE @EXITED bit
IF NOT EXISTS (SELECT * FROM CHAT_USERS
		WHERE [USER_ID] = @FROM_USER_ID AND ACCEPTED = 1 AND EXITED = 0 AND CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID))
BEGIN
	RAISERROR ('The inviting user does not belong to the chat.', 16, 1)
	RETURN
END
SELECT @CHAT_ID = CHAT_ID FROM CHATS WHERE CUID=@CUID
SELECT @ACCEPTED = ACCEPTED, @EXITED = EXITED
  FROM CHAT_USERS
  WHERE [USER_ID] = @TO_USER_ID AND CHAT_ID = @CHAT_ID
IF @@ROWCOUNT = 0 BEGIN
	INSERT INTO CHAT_USERS (CHAT_ID, [USER_ID], ACCEPTED, EXITED, USER_STATUS, INVITE_TEXT, FROM_USER_ID)
	  VALUES(@CHAT_ID, @TO_USER_ID, 0, 0, 0, @INVITE_TEXT, @FROM_USER_ID)
END ELSE BEGIN
	IF @ACCEPTED = 1 AND @EXITED = 0 BEGIN
		RAISERROR ('The user is in chat already.', 16, 1)
		RETURN
	END
	UPDATE CHAT_USERS SET FROM_USER_ID = @FROM_USER_ID, EXITED = 0, ACCEPTED = 0, INVITE_TEXT=@INVITE_TEXT
	  WHERE [USER_ID] = @TO_USER_ID AND CHAT_ID = @CHAT_ID
END
SELECT C.[NAME], C.[DESC], C.BEGIN_TIME, C.OWNER_ID,
 	@CUID as CUID, CU.INVITE_TEXT,
	U.[USER_ID], U.FIRST_NAME, U.LAST_NAME, U.EMAIL,  U.IMGROUP_ID, R.IMGROUP_NAME,
	CU.FROM_USER_ID,  FU.FIRST_NAME as FROM_FIRST_NAME, FU.LAST_NAME as FROM_LAST_NAME, FU.EMAIL as FROM_EMAIL, FU.IMGROUP_ID as FROM_IMGROUP_ID, FR.IMGROUP_NAME as FROM_IMGROUP_NAME
  FROM CHAT_USERS CU
		JOIN [USER] U ON (CU.[user_id] = U.[user_id])
		JOIN IMGROUPS R ON (U.IMGROUP_ID = R.IMGROUP_ID)
		JOIN Chats C ON (CU.chat_id = C.chat_id)
		JOIN [User] FU ON (CU.from_user_id = FU.[user_id])
		JOIN IMGROUPS FR ON (FU.IMGROUP_ID = FR.IMGROUP_ID)
  WHERE CU.[USER_ID] = @TO_USER_ID AND CU.CHAT_ID = @CHAT_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_LEAVE_CHAT
	@CUID as char(36),
	@USER_ID as int
as
SET NOCOUNT ON
UPDATE CHAT_USERS SET USER_STATUS = 0, EXITED = 1
  WHERE [USER_ID] = @USER_ID AND CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID)
IF @@ROWCOUNT = 0 BEGIN
	RAISERROR ('The user does not belong to the chat.', 16, 1)
	RETURN
END
SELECT @CUID as CUID, U.[USER_ID], U.LOGIN, U.FIRST_NAME, U.LAST_NAME, U.EMAIL, U.IMGROUP_ID, R.IMGROUP_NAME
  FROM [USER] U
	JOIN IMGROUPS R ON (U.IMGROUP_ID = R.IMGROUP_ID)
WHERE U.[USER_ID] = @USER_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_LOAD_CHAT_INVITES
	@USER_ID as int
as
SELECT C.[NAME], C.[DESC], C.BEGIN_TIME, C.OWNER_ID,  C.CUID,
	CU.INVITE_TEXT,
	CU.[USER_ID],
	CU.FROM_USER_ID, U.FIRST_NAME as FROM_FIRST_NAME, U.LAST_NAME as FROM_LAST_NAME, U.EMAIL as FROM_EMAIL, U.IMGROUP_ID as FROM_IMGROUP_ID, R.IMGROUP_NAME as FROM_IMGROUP_NAME
  FROM CHAT_USERS CU
	JOIN CHATS C ON (CU.CHAT_ID = C.CHAT_ID)
	JOIN [USER] U ON (CU.FROM_USER_ID = U.[USER_ID])
	JOIN IMGROUPS R ON (U.IMGROUP_ID = R.IMGROUP_ID)
  WHERE CU.[USER_ID] = @USER_ID AND ACCEPTED = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_LOAD_CHAT_MESS
	@CUID as char(36),
	@COUNT as int
as
SET NOCOUNT ON
CREATE TABLE #TempItems(
	[ID] int IDENTITY,
	[USER_ID] int,
	FIRST_NAME nvarchar(50),
	LAST_NAME nvarchar(50),
	SEND_TIME int,
	CHAT_MESS_ID int,
	MESS_TEXT nvarchar(1024)
)
INSERT INTO #TempItems
	SELECT CM.[USER_ID], U.FIRST_NAME, U.LAST_NAME, CM.SEND_TIME, CM.CHAT_MESS_ID as [ID], CM.MESS_TEXT
	  FROM CHAT_MESS CM JOIN [USER] U ON (CM.[USER_ID] = U.[USER_ID])
	  WHERE CM.CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID)
	  ORDER BY CM.SEND_TIME DESC
SELECT * FROM #TempItems WHERE [ID] <= @COUNT
drop table #TempItems
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_LOAD_CHAT_USERS
	@CUID as char(36),
	@USER_STATUS as bit
as
SELECT C.[USER_ID], C.USER_STATUS AS STATUS, U.LOGIN, U.FIRST_NAME, U.LAST_NAME, U.EMAIL, U.IMGROUP_ID, R.IMGROUP_NAME, C.[TIME]
  FROM CHAT_USERS C
	JOIN [USER] U ON (C.[USER_ID] = U.[USER_ID])
	JOIN IMGROUPS R ON (U.IMGROUP_ID = R.IMGROUP_ID)
WHERE C.CHAT_ID IN (SELECT CHAT_ID FROM CHATS WHERE CUID = @CUID) AND
	C.EXITED = 0 AND C.ACCEPTED = 1 AND (C.USER_STATUS = @USER_STATUS OR @USER_STATUS = 0)
	AND U.is_active = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_LOAD_CHATS
	@USER_ID as int,
	@MODE as tinyint
as
SELECT CUID, [NAME], [DESC], OWNER_ID, BEGIN_TIME
  FROM CHATS
  WHERE CHAT_ID IN
	(SELECT CHAT_ID
	  FROM CHAT_USERS
	  WHERE [USER_ID] = @USER_ID AND EXITED = 0 AND ACCEPTED = 1 AND (@MODE = 0 OR USER_STATUS = @MODE))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].IBN_SEND_CHAT_MESS
	@CUID as char(36) ,
	@MID as char(36) ,
	@USER_ID as int ,
	@MESS_TEXT as ntext,
	@SEND_TIME as int
as
SET NOCOUNT ON
DECLARE @CHAT_MESS_ID int
DECLARE @CHAT_ID int
SELECT @CHAT_ID = CHAT_ID FROM CHATS WHERE CUID = @CUID
IF @@ROWCOUNT = 0
BEGIN
	RAISERROR ('Chat does not exist.', 16, 1)
	RETURN
END
IF NOT EXISTS(SELECT * FROM CHAT_USERS WHERE CHAT_ID = @CHAT_ID AND [USER_ID] = @USER_ID AND EXITED = 0 AND ACCEPTED = 1)
BEGIN
	RAISERROR ('Access denied.', 16, 1)
	RETURN
END
IF EXISTS (SELECT * FROM Chat_Mess WHERE MID = @MID AND [USER_ID] = @USER_ID)
	SELECT @CUID AS CUID, CHAT_MESS_ID, [USER_ID], MESS_TEXT, SEND_TIME
	  FROM CHAT_MESS
	  WHERE MID = @MID AND [USER_ID] = @USER_ID
ELSE BEGIN
	INSERT INTO CHAT_MESS (CHAT_ID, MID, [USER_ID], MESS_TEXT, SEND_TIME)
	  VALUES(@CHAT_ID, @MID, @USER_ID, @MESS_TEXT, @SEND_TIME)
	SELECT @CHAT_MESS_ID = @@identity
	SELECT @CUID AS CUID, CHAT_MESS_ID, [USER_ID], MESS_TEXT, SEND_TIME FROM CHAT_MESS WHERE CHAT_MESS_ID = @CHAT_MESS_ID
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].OM_ADD_ACTIVE_USER
	@USER_ID INT,
	@SID CHAR(36),
	@STATUS INT,
	@DT_BEGIN INT,
	@RETVAL INT OUTPUT
AS
BEGIN TRAN
	DELETE FROM Sessions WHERE [user_id] = @USER_ID AND Dt_Begin = @DT_BEGIN
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE Sessions SET Dt_End = Dt_Begin WHERE [user_id] = @USER_ID AND Dt_End IS NULL
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	INSERT INTO Sessions ([user_id], Dt_Begin) VALUES (@USER_ID, @DT_BEGIN)
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE [USER] SET status_time = @DT_BEGIN WHERE [user_id] = @USER_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	INSERT INTO Active_User ([user_id], sid, status) VALUES (@USER_ID, @SID, @STATUS)
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	SET @RETVAL = @@ROWCOUNT
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_ADD_AUTH_REPLY
	@FROM_USER_ID INT,
	@TO_USER_ID INT,
	@FLAG INT
AS
SET NOCOUNT ON
DECLARE @S_OFFLINE INT
SET @S_OFFLINE = 0
DECLARE @USER_ID int
SET @USER_ID = -1
UPDATE Auth_List SET deny_flag = @FLAG WHERE from_user_id = @TO_USER_ID AND to_user_id = @FROM_USER_ID
IF @@ROWCOUNT > 0
	SET @USER_ID = @FROM_USER_ID
SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_OFFLINE AS status, U.imgroup_id, R.imgroup_name, @FLAG AS deny_flag
 FROM [user] U
	JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
 WHERE U.[user_id] = @USER_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_ADD_AUTH_REQUEST
	@FROM_USER_ID INT,
	@TO_USER_ID INT,
	@MESS_TEXT nVARCHAR(1024)
AS
SET NOCOUNT ON
DECLARE @REQUEST_FLAG INT
DECLARE @S_OFFLINE INT
SET @S_OFFLINE = 0
DECLARE @STATUS INT
DECLARE @FROM_IMGROUP_ID int
SELECT @FROM_IMGROUP_ID = imgroup_id FROM [User] WHERE [user_id] = @FROM_USER_ID
IF EXISTS (SELECT * FROM [User] WHERE [user_id] = @TO_USER_ID AND
		(imgroup_id = @FROM_IMGROUP_ID OR imgroup_id IN
		(SELECT dep_imgroup_id FROM Dependences WHERE imgroup_id = @FROM_IMGROUP_ID)))
BEGIN
	SET @REQUEST_FLAG = 3
	INSERT INTO Auth_List (from_user_id, to_user_id, mess_text, deny_flag)
	  VALUES (@FROM_USER_ID, @TO_USER_ID, @MESS_TEXT, @REQUEST_FLAG)
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_OFFLINE AS status, U.imgroup_id, R.imgroup_name, @REQUEST_FLAG AS deny_flag
	 FROM [user] U
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	 WHERE U.[user_id] = @TO_USER_ID
END
ELSE BEGIN
	SET @REQUEST_FLAG = 0
	INSERT INTO Auth_List (from_user_id, to_user_id, mess_text, deny_flag)
	  VALUES (@FROM_USER_ID, @TO_USER_ID, @MESS_TEXT, @REQUEST_FLAG)
	SELECT @STATUS = status FROM Active_User WHERE [user_id] = @FROM_USER_ID
	IF @@ROWCOUNT = 0
		SET @STATUS = @S_OFFLINE
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @STATUS AS status, U.imgroup_id, R.imgroup_name, A.mess_text, 0 AS deny_flag
	  FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
		JOIN Auth_List A ON (U.[user_id] = A.from_user_id)
	  WHERE A.from_user_id = @FROM_USER_ID AND A.to_user_id = @TO_USER_ID
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_ADD_FILE
	@FROM_USER_ID INT,
	@TO_USER_ID INT,
	@FILE_ID CHAR(36),
	@FILE_NAME nVARCHAR(255),
	@FILE_DESCR nVARCHAR(1024),
	@FILE_DATE INT,
	@FILE_SIZE INT
AS
SET NOCOUNT ON
DECLARE @FILE_STATUS INT
SET @FILE_STATUS = 0
INSERT INTO [File] (from_user_id, to_user_id, [file_id], [file_name], file_descr, file_date, file_status, file_size)
  VALUES (@FROM_USER_ID,	@TO_USER_ID, @FILE_ID, @FILE_NAME, @FILE_DESCR, @FILE_DATE, @FILE_STATUS, @FILE_SIZE)
SELECT from_user_id AS sender, 0 AS recipient, [file_id], [file_name], file_descr, file_date, file_size
  FROM [File]
  WHERE to_user_id = @TO_USER_ID AND [file_id] = @FILE_ID
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_ADD_USER
	@USER_ID INT,
	@CONT_USER_ID INT,
	@LIST_TYPE INT,
	@HAS_AUTHORIZATION bit,
	@RETVAL INT OUTPUT
AS
INSERT INTO Contact_List ([user_id], cont_user_id, list_type, HAS_AUTHORIZATION)
  VALUES (@USER_ID, @CONT_USER_ID, @LIST_TYPE, @HAS_AUTHORIZATION)
SET @RETVAL = @@ROWCOUNT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_AUTHENTICATION
	@LOGIN NVARCHAR(50),
	@PASSWORD  NVARCHAR(50),
	@DOMAIN NVARCHAR(255),
	@RETVAL INT OUTPUT
AS
SELECT @RETVAL = [user_id]
  FROM [User]
  WHERE login = @LOGIN  AND [password] = @PASSWORD AND is_active = 1 AND
	imgroup_id NOT IN (SELECT imgroup_id FROM IMGROUPS WHERE IS_INTERNAL =1) AND
	imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM Companies WHERE DOMAIN = @DOMAIN AND is_active = 1 AND use_im = 1))
IF @@ROWCOUNT = 0
	SET @RETVAL = -1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_BINARYCREATE
	@fid char(36),
	@retval int output
AS
IF(SELECT count(*) FROM BINARY_DATA f WHERE f.fid=@fid)>0
BEGIN
	SET @retval = (SELECT f.id FROM BINARY_DATA f WHERE f.fid=@fid)
	RETURN
END
INSERT INTO BINARY_DATA (fid, filedata) VALUES (@fid, '')
SET @retval=@@identity
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_BINARYDELETE
	@fid char(36),
	@retval int output
AS
IF(SELECT count(*) FROM BINARY_DATA f WHERE f.fid=@fid)=0
BEGIN
	SET @retval = 0
	RETURN
END
BEGIN TRAN
DELETE FROM BINARY_DATA WHERE fid=@fid
IF @@error=0 COMMIT ELSE ROLLBACK
SET @retval=@@rowcount
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_BINARYREAD
	@id int,
	@offset int,
	@size int
AS
IF((SELECT count(*) FROM BINARY_DATA f WHERE f.id=@id)=0) RETURN
DECLARE @len as int
SET @len=(SELECT datalength(FileData) FROM BINARY_DATA f WHERE f.id=@id)
IF @offset>=@len BEGIN
	SELECT NULL as FileData FROM BINARY_DATA f WHERE f.id=@id
	RETURN
END
DECLARE @szToRead as int
SET @szToRead = (SELECT CASE WHEN @size<@len-@offset THEN @size ELSE @len-@offset END)
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(FileData) FROM BINARY_DATA f WHERE f.id=@id
READTEXT BINARY_DATA.FileData @ptrval @offset @szToRead
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_BINARYWRITE
	@id int,
	@data image,
	@retval int output
AS
IF not EXISTS(SELECT null FROM BINARY_DATA f WHERE f.id=@id) BEGIN
	SET @retval = 0
	RETURN
END
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(FileData) FROM BINARY_DATA f WHERE f.id = @id
UPDATETEXT BINARY_DATA.FileData @ptrval NULL NULL @data
SET @retval = @@rowcount
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_CHANGE_STATUS
	@USER_ID INT,
	@STATUS INT,
	@RETVAL INT OUTPUT
AS
DECLARE @DT_BEGIN int
SET @DT_BEGIN = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())
BEGIN TRAN
	UPDATE [USER] SET status_time = @DT_BEGIN WHERE [user_id] = @USER_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE Active_User SET status = @STATUS WHERE [user_id] = @USER_ID
	SET @RETVAL = @@ROWCOUNT
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_CHECK_ACTIVE_USER
	@sid  as char(36),
	@retval as int output
AS
SELECT @retval = [user_id] FROM ACTIVE_USER WHERE sid = @sid
IF @@rowcount = 0
	set @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_CHECK_IGNORE
	@USER_ID INT,
	@CONT_USER_ID INT,
	@LIST_TYPE INT,
	@RETVAL INT OUTPUT
AS
SELECT @RETVAL = COUNT(*) FROM Contact_List
  WHERE [user_id] = @CONT_USER_ID AND cont_user_id = @USER_ID AND list_type = @LIST_TYPE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_CONFIRM_FILE
	@FID CHAR(36),
	@FILE_STATUS INT,
	@USER_ID INT,
	@RETVAL INT OUTPUT
AS
DECLARE @DEL_STATUS int
SET @DEL_STATUS = 2
UPDATE [File] SET file_status = @FILE_STATUS WHERE [file_id] = @FID AND to_user_id = @USER_ID
SELECT @RETVAL = COUNT(*) FROM [File] WHERE file_status <> @DEL_STATUS AND [file_id] = @FID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_CONFIRM_MESSAGE
	@MID CHAR(36),
	@SID CHAR(36),
	@USER_ID INT,
	@RETVAL INT OUTPUT
AS
UPDATE User_Mess SET to_user_sid = @SID WHERE mess_id = @MID AND to_user_id = @USER_ID
SET @RETVAL = @@ROWCOUNT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_DELETE_AUTH_REQUEST
	@FROM_USER_ID INT,
	@TO_USER_ID INT,
	@RETVAL INT OUTPUT
AS
SELECT @RETVAL = deny_flag FROM Auth_List WHERE from_user_id = @FROM_USER_ID AND to_user_id = @TO_USER_ID
IF @@ROWCOUNT = 0
	SET @RETVAL = 0
DELETE FROM Auth_List WHERE from_user_id = @FROM_USER_ID AND to_user_id = @TO_USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_DELETE_USER
	@USER_ID INT,
	@CONT_USER_ID INT,
	@LIST_TYPE INT,
	@RETVAL INT OUTPUT
AS
DELETE FROM Contact_List WHERE [user_id] = @USER_ID AND cont_user_id = @CONT_USER_ID AND list_type = @LIST_TYPE
SET @RETVAL = @@ROWCOUNT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_GET_HIGHT_VERSIONS
	@build as int,
	@productguid as char(36)
AS
SELECT V.build, V.[date], U.[description]  FROM DL_VERSIONS V, DL_UPDATES U, DL_PRODUCTS AS P
	WHERE (V.ProductId=P.ProductId) AND(P.ProductGuid=@productguid) AND (U.VersionId = V.VersionId) AND (V.build > @build)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_GET_LOGIN_DOMAIN
	@user_id int
AS
SELECT login, domain
  FROM [User] U
	JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	JOIN Companies C ON (R.company_id = C.company_id)
  WHERE U.[user_id] = @user_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_GET_UPDATE_DESCRIPTIONS
      @build as int
AS
SELECT [description] FROM Updates WHERE build = @build
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_GET_USER_INFO
	@USER_ID INT,
	@INFO_TYPE INT,
	@TIME INT
AS
SET NOCOUNT ON
DECLARE @S_OFFLINE INT
SET @S_OFFLINE = 0
IF @INFO_TYPE = 1 BEGIN
	DECLARE @STATUS INT
	SELECT @STATUS=status FROM Active_user WHERE [user_id] = @USER_ID
	IF @@ROWCOUNT = 0
		SET @STATUS = @S_OFFLINE
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @STATUS AS status, U.imgroup_id, R.imgroup_name, U.status_time as [time]
  	  FROM [user] U
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
  	  WHERE U.[user_id] = @USER_ID
END
ELSE IF @INFO_TYPE = 2 BEGIN
	SELECT * FROM User_Details WHERE [user_id] = @USER_ID
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_IMGROUPID_BY_LOG
	@LOGIN VARCHAR(50),
	@PASSWORD  VARCHAR(50),
	@RETVAL INT OUTPUT
AS
SELECT @RETVAL = [imgroup_id] FROM [User] WHERE login = @LOGIN AND [password] = @PASSWORD
IF @@ROWCOUNT = 0
	SET @RETVAL = -1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_KILL_ALL
	@TIME int,
	@RETVAL INT OUTPUT
AS
BEGIN TRAN
	UPDATE Sessions SET Dt_End = Dt_Begin WHERE Dt_End IS NULL
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE CHAT_USERS SET USER_STATUS = 0, [TIME] = @TIME WHERE USER_STATUS <> 0
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE [USER] SET status_time = @TIME WHERE [user_id] IN (SELECT [user_id] FROM Active_User)
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	DELETE FROM Active_User
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	SET @RETVAL = @@ROWCOUNT
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_ACTIVE_USERS_BY_IMGROUP
	@IMGROUP_ID int
AS
SELECT [user_id] FROM [User]
 WHERE imgroup_id = @IMGROUP_ID AND [user_id] IN (SELECT [user_id] FROM Active_User)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_AUTH_LIST
	@USER_ID INT,
	@STATUS INT,
	@TIME INT
AS
DECLARE @S_AUTH INT
SET @S_AUTH = 7
IF @STATUS = 0
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_AUTH AS status, U.imgroup_id, R.imgroup_name, A.deny_flag, ' ' AS result, 0 as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
		JOIN Auth_List A ON (U.[user_id] = A.to_user_id)
	  WHERE  A.from_user_id = @USER_ID AND A.deny_flag = 0 AND U.is_active = 1
ELSE
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_AUTH AS status, U.imgroup_id, R.imgroup_name, A.deny_flag, ' ' AS result, 0 as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
		JOIN Auth_List A ON (U.[user_id] = A.to_user_id)
	  WHERE  A.from_user_id = @USER_ID AND A.deny_flag <> 0 AND U.is_active = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_AUTH_LIST_REV
	@USER_ID INT
AS
DECLARE @NO_REPLY INT
SET @NO_REPLY = 0
DECLARE @S_AUTH INT
SET @S_AUTH = 7
SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_AUTH AS status, U.imgroup_id, R.imgroup_name, A.mess_text
    FROM [User] U
	JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	JOIN Auth_List A ON (U.[user_id] = A.from_user_id)
  WHERE  A.to_user_id = @USER_ID AND A.deny_flag = @NO_REPLY AND U.is_active = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_DETAILS
	@USER_ID INT
AS
SELECT [user_id], login AS [name], first_name, last_name, eMail, imgroup_name AS user_type
  FROM [user] U
	JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
  WHERE U.[user_id] = @USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_FILES
	@TO_USER_ID INT,
	@FILE_STATUS INT
AS
SELECT from_user_id AS sender, 0 AS recipient, [file_id], [file_name], file_descr, file_date, file_size
  FROM [File]
  WHERE to_user_id = @TO_USER_ID AND file_status = @FILE_STATUS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_LIST
	@USER_ID INT,
	@LIST_TYPE INT,
	@TIME INT
AS
DECLARE @S_OFFLINE INT
SET @S_OFFLINE = 0
DECLARE @S_APP INT
SET @S_APP = 1
DECLARE @CONTACT_LIST_TYPE INT
SET @CONTACT_LIST_TYPE = 1
DECLARE @is_global bit
DECLARE @company_id int
SELECT @company_id = company_id, @is_global = is_global
 FROM IMGROUPS WHERE imgroup_id IN (SELECT imgroup_id FROM [USER] where [user_id]=@USER_ID)
IF @is_global = 0
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, A.status, U.imgroup_id, R.imgroup_name, U.status_time as time
	  FROM [User] U
		JOIN Contact_List C ON (U.[user_id] = C.cont_user_id)
		JOIN Active_User A ON (C.cont_user_id = A.[user_id])
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	  WHERE C.[user_id]=@USER_ID AND  C.list_type = @LIST_TYPE AND U.is_active = 1
	UNION ALL
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_OFFLINE AS status, U.imgroup_id, R.imgroup_name, U.status_time  as time
	    FROM [User] U
		JOIN Contact_list C ON (U.[user_id]=C.cont_user_id)
		JOIN IMGROUPS R ON (U.imgroup_id=R.imgroup_id)
	    WHERE C.[user_id]=@USER_ID AND  C.list_type = @LIST_TYPE AND C.cont_user_id NOT IN (SELECT [user_id] FROM Active_User) AND U.is_active = 1
	UNION ALL
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_APP AS status, U.imgroup_id, R.imgroup_name, U.status_time as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id=R.imgroup_id)
	    WHERE R.IS_INTERNAL = 1  AND @CONTACT_LIST_TYPE = @LIST_TYPE AND R.company_id = @company_id
	UNION
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, A.status, U.imgroup_id, R.imgroup_name, U.status_time  as time
	    FROM [User] U
		JOIN Active_User A ON (U.[user_id] = A.[user_id])
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	    WHERE R.is_global = 1 AND U.is_active = 1
	UNION
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_OFFLINE AS status, U.imgroup_id, R.imgroup_name, U.status_time as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id=R.imgroup_id)
	    WHERE U.[user_id] NOT IN (SELECT [user_id] FROM Active_User) AND R.is_global = 1 AND U.is_active = 1
		AND R.company_id IN (SELECT company_id FROM COMPANIES WHERE is_active = 1)
ELSE
	SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, A.status, U.imgroup_id, R.imgroup_name, U.status_time as time
	  FROM [User] U
		JOIN Active_User A ON (U.[user_id] = A.[user_id])
		JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	  WHERE U.[user_id] != @USER_ID AND U.is_active = 1
	UNION ALL
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_OFFLINE AS status, U.imgroup_id, R.imgroup_name, U.status_time as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id=R.imgroup_id)
	    WHERE U.[user_id] NOT IN (SELECT [user_id] FROM Active_User) AND U.is_active = 1 AND U.[user_id] != @USER_ID
		AND R.company_id IN (SELECT company_id FROM COMPANIES WHERE is_active = 1)
	UNION ALL
	  SELECT U.[user_id], U.login, U.first_name, U.last_name, U.eMail, @S_APP AS status, U.imgroup_id, R.imgroup_name, U.status_time as time
	    FROM [User] U
		JOIN IMGROUPS R ON (U.imgroup_id=R.imgroup_id)
	    WHERE R.IS_INTERNAL = 1  AND @CONTACT_LIST_TYPE = @LIST_TYPE AND R.company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_LIST_REV
	@USER_ID INT,
	@LIST_TYPE INT
AS
DECLARE @is_global bit
SELECT @is_global = is_global  FROM IMGROUPS WHERE imgroup_id IN (SELECT imgroup_id FROM [USER] where [user_id]=@USER_ID)
IF @is_global = 0
	SELECT [user_id]
	  FROM Contact_list
	  WHERE cont_user_id = @USER_ID AND list_type = @LIST_TYPE AND [user_id] IN (SELECT [user_id] FROM Active_User)
	UNION
	  SELECT [user_id]
	    FROM [USER] WHERE [user_id] != @USER_ID
		AND [user_id] IN (SELECT [user_id] FROM Active_User)
		AND imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)
ELSE
	SELECT [user_id]
	  FROM Active_User
	  WHERE [user_id] != @USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_MESS
	@USER_ID INT
AS
SELECT TOP 50 from_user_id AS sender, 0 AS recipient, mess_id AS mid, mess_text, send_time
  FROM User_Mess
  WHERE to_user_id=@USER_ID AND to_user_sid IS NULL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_SHORT_DETAILS
	@USER_ID INT
AS
SELECT [user_id], imgroup_id, login, first_name, last_name, email
  FROM [user]
  WHERE [user_id] = @USER_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_SIDS
	@USER_ID INT,
	@FROM INT,
	@TO INT
AS
SET NOCOUNT ON
SELECT from_user_sid AS sid, COUNT(mess_id) AS mess_count INTO #temp FROM USER_MESS
 WHERE from_user_id = @USER_ID AND send_time >= @FROM AND send_time <= @TO AND from_user_sid IS NOT NULL
 GROUP BY from_user_sid
UNION ALL
SELECT to_user_sid AS sid, COUNT(mess_id)  AS mess_count FROM USER_MESS
 WHERE to_user_id = @USER_ID AND send_time >= @FROM AND send_time <= @TO AND to_user_sid IS NOT NULL
 GROUP BY to_user_sid
SET NOCOUNT OFF
SELECT sid, SUM(mess_count) AS mess_count FROM #temp
 GROUP BY sid
DROP TABLE #temp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOAD_STUBS
	@User_Id int
AS
SELECT stub_id, short_name, full_name, tooltip, url, open_window, width, height, CASE WHEN Icon IS NULL THEN 0 ELSE 1 END AS ShowIcon, IsInternal
 FROM Stubs
 WHERE stub_id IN (SELECT stub_id FROM STUB_USER WHERE [user_id] = @User_Id)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_LOGOFF
	@USER_ID INT,
	@DT_END INT,
	@RETVAL INT OUTPUT
AS
BEGIN TRAN
	UPDATE Sessions SET Dt_End = @DT_END WHERE [user_id] = @USER_ID AND Dt_End IS NULL
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	UPDATE [USER] SET status_time = @DT_END WHERE [user_id] = @USER_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	DELETE FROM Active_User WHERE [user_id] = @USER_ID
	IF @@error != 0
	BEGIN
		ROLLBACK TRAN
		RETURN
	END
	SET @RETVAL = @@ROWCOUNT
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_SEARCH_USER
	@USER_ID INT,
	@SQL VARCHAR(1024)
AS
SET NOCOUNT ON
EXEC (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE [dbo].OM_SEARCH_USERS
	@lg as nvarchar(50),
	@fn as nvarchar(50),
	@ln as nvarchar(50),
	@em as nvarchar(50),
	@COMPANY_ID as int
AS
SELECT [user_id], login, first_name, last_name, email
  FROM [User] WHERE(
	(login LIKE '%' + @lg + '%' OR @lg IS NULL) AND
	(first_name LIKE '%' + @fn + '%' OR @fn IS NULL) AND
	(last_name LIKE '%' + @ln + '%' OR @ln IS NULL) AND
	(email LIKE '%' + @em + '%' OR @em IS NULL)) AND is_active = 1 AND
	imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE Company_id = @COMPANY_ID) AND
	imgroup_id NOT IN (SELECT imgroup_id FROM IMGROUPS WHERE IS_INTERNAL =1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_SEND_MESSAGE
	@FROM_USER_ID INT,
	@TO_USER_ID INT,
	@MESS_ID CHAR(36) = NULL,
	@MESS_TEXT ntext,
	@SEND_TIME INT,
	@USER_SID CHAR(36)
AS
SET NOCOUNT ON
IF @MESS_ID IS NULL OR @MESS_ID = ''
	SET @MESS_ID = NEWID()
IF NOT EXISTS(SELECT * FROM User_Mess WHERE to_user_id = @TO_USER_ID AND mess_id = @MESS_ID)
	INSERT INTO User_Mess (from_user_id, to_user_id, mess_id, mess_text, send_time, from_user_sid)
	  VALUES (@FROM_USER_ID, @TO_USER_ID, @MESS_ID, @MESS_TEXT, @SEND_TIME, @USER_SID)
SELECT from_user_id AS sender, 0 AS recipient, mess_id AS mid, mess_text, send_time
  FROM User_Mess
  WHERE to_user_id = @TO_USER_ID AND mess_id = @MESS_ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].OM_SYNC_HISTORY
	@USER_ID INT,
	@SID CHAR(36)
AS
SELECT from_user_id AS sender, to_user_id AS recipient, mess_id AS mid, mess_text, send_time
  FROM User_Mess
  WHERE from_user_sid = @SID OR to_user_sid = @SID
 ORDER BY send_time
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE [dbo].REP_GET_AUTH_USERS
	@startdate as datetime,
	@enddate as datetime,
	@TimeOffset as int,
	@company_id as int,
	@retval int output
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) +@TimeOffset
SELECT  @retval=count(distinct user_id)
FROM  Sessions
WHERE user_id IN (SELECT user_id FROM [User] WHERE imgroup_id in (SELECT imgroup_id FROM imgroups WHERE company_id = @company_id))
	AND dt_begin BETWEEN  @beg_sec AND @end_sec
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE [dbo].REP_GET_BYTES_TRANSFERRED
	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@total as int,
	@TimeOffset as int,
	@retval as int output
AS
IF @total = 0
BEGIN
	DECLARE  @beg_sec int, @end_sec int
	SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
	SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) +@TimeOffset
	SELECT  @retval=SUM(File_size)
	FROM (
			SELECT DISTINCT F.[File_id], F.File_size
			FROM dbo.[File] AS F,dbo.[USER] AS U, IMGROUPS AS R
			WHERE F.FROM_USER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
			AND F.FILE_DATE BETWEEN @beg_sec AND @end_sec
		  ) A
	if @retval is NULL
	set @retval = 0
END
ELSE
	SELECT  @retval=SUM(File_size)
	FROM (
			SELECT DISTINCT F.[File_id], F.File_size
			FROM dbo.[File] AS F,dbo.[USER] AS U, IMGROUPS AS R
			WHERE F.FROM_USER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
		  ) A
	if @retval is NULL
	set @retval = 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_CHAT_MESSAGES
	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@total as int,
	@TimeOffset as int,
	@retval int output
AS
IF @total = 0
BEGIN
	DECLARE  @beg_sec int, @end_sec int
	SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
	SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) +@TimeOffset
	SELECT  @retval=COUNT(DISTINCT M.MID)
	FROM  CHAT_MESS AS M, CHATS AS C, dbo.[USER] AS U, IMGROUPS AS R
	WHERE M.CHAT_ID = C.CHAT_ID AND C.OWNER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
	AND M.SEND_TIME BETWEEN  @beg_sec AND @end_sec
END
ELSE
	SELECT  @retval=COUNT(DISTINCT M.MID)
	FROM  CHAT_MESS AS M, CHATS AS C, dbo.[USER] AS U, IMGROUPS AS R
	WHERE M.CHAT_ID = C.CHAT_ID AND C.OWNER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_CONFERENCES_CREATED
	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@TimeOffset as int,
	@retval int output
AS
	DECLARE  @beg_sec int, @end_sec int
	SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
	SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) + @TimeOffset
	SELECT @retval=COUNT(C.CHAT_ID)
	FROM CHATS AS C, IMGROUPS AS R,dbo.[USER] AS U
	WHERE C.OWNER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
	AND C.BEGIN_TIME BETWEEN  @beg_sec AND @end_sec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_FILES_TRANSFERRED
	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@total as int,
	@TimeOffset as int,
	@retval int output
AS
IF @total = 0
BEGIN
	DECLARE  @beg_sec int, @end_sec int
	SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
	SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) + @TimeOffset
	SELECT  @retval=COUNT(DISTINCT F.FILE_ID)
	FROM  dbo.[FILE] AS F, dbo.[USER] AS U, IMGROUPS AS R
	WHERE F.FROM_USER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
	AND F.FILE_DATE BETWEEN @beg_sec AND @end_sec
END
ELSE
	SELECT  @retval=COUNT(DISTINCT F.FILE_ID)
	FROM  dbo.[FILE] AS F, dbo.[USER] AS U, IMGROUPS AS R
	WHERE F.FROM_USER_ID=U.USER_ID AND U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_GROUP_STATS
	@imgroup_id as int,
	@fromdate as datetime,
	@todate as datetime,
	@TimeOffset as int
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec = DATEDIFF(s, '1970-01-01 00:00', @fromdate) + @TimeOffset
SET @end_sec = DATEDIFF(s, '1970-01-01 00:00', @todate) +@TimeOffset
select * from
(select count(user_id) AccActive from [user] where imgroup_id = @imgroup_id and is_active = 1) c1,
(select count(user_id) AccInactive from [user] where imgroup_id = @imgroup_id and is_active = 0) c2,
(select count(from_user_id) MsgIM from user_mess where from_user_id in (select user_id from [user] where imgroup_id = @imgroup_id) and send_time >= @beg_sec and send_time <= @end_sec) c3,
(select count(user_id) MsgConf from chat_mess where user_id in (select user_id from [user] where imgroup_id = @imgroup_id) and send_time >= @beg_sec and send_time <= @end_sec) c4,
(select count(from_user_id) TransfFiles, (case when sum(file_size) is null then 0 else sum(file_size) end) TransfBytes from [file] where from_user_id in (select user_id from [user] where imgroup_id = @imgroup_id) and file_date >= @beg_sec and file_date <= @end_sec) c5,
(select count(chat_id) Confs from chats where owner_id in (select user_id from [user] where imgroup_id = @imgroup_id) and begin_time >= @beg_sec and begin_time <= @end_sec) c6
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_GROUP_TOP10
	@imgroup_id as int,
	@fromdate as datetime,
	@todate as datetime,
	@TimeOffset as int
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec = DATEDIFF(s, '1970-01-01 00:00', @fromdate) + @TimeOffset
SET @end_sec = DATEDIFF(s, '1970-01-01 00:00', @todate) +@TimeOffset
select top 10 U1.[user_id] u_id, sum(sent) as sent
into #gr_topten
from (
select M.from_user_id id, count(distinct M.mess_id) as sent
from  USER_MESS AS M, [USER] AS U
where M.FROM_USER_ID in (select user_id from [User] where imgroup_id = @imgroup_id)
AND   M.SEND_TIME BETWEEN @beg_sec AND @end_sec
group by M.from_user_id
union
select M.user_id id, count(distinct M.MID) as sent
from CHAT_MESS AS M
WHERE M.user_id in (select user_id from [User] where imgroup_id = @imgroup_id)
AND M.SEND_TIME BETWEEN @beg_sec AND @end_sec
group by M.user_id
) A, [USER] as U1
where id = U1.[USER_ID]
group by U1.[user_id]
order by sent desc
select u_id [user_id], sum(sent) sent, sum(rcvd) received, sum(files) files, sum(logins) logins
from (
select u_id, sent, 0 rcvd, 0 files, 0 logins
from #gr_topten
group by u_id, sent
union
select T.u_id, 0 sent, count(M.mess_id) rcvd, 0 files, 0 logins
from #gr_topten T, USER_MESS M, [User] U
where T.u_id = U.user_id and U.user_id = M.to_user_id
and M.send_time between @beg_sec and @end_sec
group by T.u_id, T.sent
union
select T.u_id, 0 sent, count(M.mid) rcvd, 0 files, 0 logins
from #gr_topten T, Chat_mess M, [User] U
where T.u_id = U.user_id and U.user_id = M.user_id
and M.send_time between @beg_sec and @end_sec
group by T.u_id, T.sent
union
select T.u_id, 0 sent, 0 rcvd, count(M.file_id) files, 0 logins
from #gr_topten T, [File] M, [User] U
where T.u_id = U.user_id and U.user_id = M.from_user_id
and M.file_date between @beg_sec and @end_sec
group by T.u_id, T.sent
union
select T.u_id, 0 sent, 0 rcvd, 0 files, count(M.dt_begin) logins
from #gr_topten T, Sessions M, [User] U
where T.u_id = U.user_id and U.user_id = M.user_id
and M.dt_begin between @beg_sec and @end_sec
group by T.u_id, T.sent
) B
group by u_id
order by sent desc
drop table #gr_topten
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE REP_GET_IM_MESSAGES
	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@use_dates as bit,
	@internal as bit,
	@TimeOffset as int
AS
DECLARE  @beg_sec int, @end_sec int, @MesSent as int, @MesRec as int
IF @use_dates = 1
BEGIN
	SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) + @TimeOffset
	SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) + @TimeOffset
	set @MesSent=(SELECT COUNT(DISTINCT mess_id)
		FROM  User_mess
		WHERE  from_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_internal = @internal AND company_id = @company_id))
			AND (send_time BETWEEN  @beg_sec AND @end_sec) AND (from_user_id NOT IN( SELECT [user_id] FROM [User] Where login='alert' )))
	set @MesRec=(SELECT  COUNT(DISTINCT mess_id)
		FROM  User_mess
		WHERE  to_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_internal = @internal AND company_id = @company_id))
			AND (send_time BETWEEN  @beg_sec AND @end_sec) AND (from_user_id NOT IN( SELECT [user_id] FROM [User] Where login='alert' )))
END
ELSE
BEGIN
	set @MesSent=(SELECT  COUNT(DISTINCT mess_id)
		FROM  User_mess
		WHERE from_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_internal = @internal AND company_id = @company_id))
			AND (from_user_id NOT IN( SELECT [user_id] FROM [User] Where login='alert' )))
	set @MesRec=(SELECT  COUNT(DISTINCT mess_id)
		FROM  User_mess
		WHERE to_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_internal = @internal AND company_id = @company_id))
			AND (from_user_id NOT IN( SELECT [user_id] FROM [User] Where login='alert' )))
END
Select @MesSent as MessagesSent, @MesRec as MessagesReceived
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_IMGROUPS
	@imgroup_id as int,
	@company_id as int
as
SELECT R.imgroup_id AS IMGroupId, R.imgroup_name AS IMGroupName, (R.imgroup_name+'@'+C.domain) AS CanonName
  FROM IMGROUPS AS R, Companies AS C
  WHERE (R.imgroup_id = @imgroup_id or @imgroup_id = 0)
	AND R.company_id = @company_id
	AND R.company_id = C.Company_id
  ORDER BY R.imgroup_name
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_IMSESSIONS_COUNT_GET
	@CompanyId as int,
	@StartDate as DateTime,
	@EndDate as DateTime,
	@TimeOffset as int
as
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @StartDate) +@TimeOffset
SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @EndDate) +@TimeOffset
SELECT Distinct(S.[User_Id])
  FROM SESSIONS AS S, [USER] AS U
Where (dt_begin>=@beg_sec)AND(dt_begin<=@end_sec)
	AND(S.[User_ID]=U.[User_ID])
	AND(U.[User_Id] IN
		 (Select U.[User_Id] From [User] AS U, IMGROUPS AS R Where U.IMGROUP_ID = R.IMGROUP_ID AND R.Company_ID=@CompanyID) )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE  PROCEDURE [dbo].REP_GET_ONLINE_USERS_COUNT
	@company_id as int,
	@retval int output
AS
SELECT  @retval = COUNT(*)
  FROM ACTIVE_USER A
	JOIN [USER] U ON (A.[user_id] = U.[user_id])
	JOIN IMGROUPS G ON (U.imgroup_id = G.imgroup_id)
  WHERE G.Company_id = @company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_QUICK_USAGE_STATS
	@user_id as int
AS
DECLARE @MessSent int
SELECT @MessSent = COUNT(distinct mess_id)
  FROM USER_MESS
  WHERE from_user_id  = @user_id
DECLARE @MessReceived int
SELECT @MessReceived = COUNT(distinct mess_id)
  FROM USER_MESS
  WHERE to_user_id  = @user_id AND
	from_user_id NOT IN (SELECT [user_id] FROM [User] where login='alert')
SELECT @MessSent AS MessSent, @MessReceived AS MessReceived
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_SESSIONS
	@StartDate as DateTime,
	@EndDate as DateTime,
	@TimeOffset as int
as
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @StartDate) +@TimeOffset
SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @EndDate) +@TimeOffset
SELECT dt_begin-@TimeOffset AS StartSession
  FROM SESSIONS
Where (dt_begin>=@beg_sec)AND(dt_begin<=@end_sec)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOP10_WRITERS
 	@startdate as datetime,
	@enddate as datetime,
	@company_id as int,
	@TimeOffset as int
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec=DATEDIFF(s, '1970-01-01 00:00', @startdate) +@TimeOffset
SET @end_sec=DATEDIFF(s, '1970-01-01 00:00', @enddate) +@TimeOffset
select top 10 U1.[User_ID],sum(count) as messages
from ( select M.from_user_id id,count(distinct M.mess_id) as count from  USER_MESS AS M
where M.FROM_USER_ID in (select user_id from [User] where imgroup_id in (select imgroup_id from IMGROUPS where is_internal = 0 AND company_id = @company_id))
AND   M.SEND_TIME BETWEEN @beg_sec AND @end_sec AND (M.from_user_id NOT IN( SELECT [user_id] FROM [User] Where login='alert' ))
group by M.from_user_id
union
select M.user_id id,count(distinct M.MID) as count
from CHAT_MESS M
WHERE M.user_id in (select user_id from [User] where imgroup_id in (select imgroup_id from IMGROUPS where company_id = @company_id))
AND M.SEND_TIME BETWEEN @beg_sec AND @end_sec
group by M.user_id
) A, [USER] as U1
where id = U1.[USER_ID]
group by U1.[User_id]
order by messages desc
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOTAL_ACTIVE_USERS
	@company_id as int,
	@retval int output
AS
Select @retval = COUNT(U.USER_ID)
FROM [USER] AS U, IMGROUPS AS R
WHERE U.IMGROUP_ID=R.IMGROUP_ID AND (R.COMPANY_ID=@company_id OR @company_id=0) AND U.IS_ACTIVE=1 AND U.Login!='alert'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOTAL_GROUPS
			@company_id as int,
			@retval int output
AS
Select @retval = COUNT(IMGROUP_ID)
FROM IMGROUPS
WHERE COMPANY_ID=@company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOTAL_INACTIVE_USERS
			@company_id as int,
			@retval int output
AS
Select @retval = COUNT(U.USER_ID)
FROM dbo.[USER] AS U, IMGROUPS AS R
WHERE U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id AND U.IS_ACTIVE=0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOTAL_STATISTICS
AS
DECLARE @v1 as int
set @v1 = (Select Count(RequestId) From Trial_Requests Where IsActive=1)
DECLARE @v2 as int
set @v2 = (Select Count(RequestId) From Trial_Requests Where IsActive=0)
DECLARE @v3 as int
set @v3 = (Select Count(company_id) From Companies Where company_type=1)
Select COUNT(Company_Id) AS CompaniesCount, @v1 AS ActiveTrialsCount, @v2 AS PendingTrialsCount, @v3 AS BillableCustomers
FROM dbo.[Companies]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_TOTAL_USERS
			@company_id as int,
			@retval int output
AS
Select @retval = COUNT(U.USER_ID)
FROM dbo.[USER] AS U, IMGROUPS AS R
WHERE U.IMGROUP_ID=R.IMGROUP_ID AND R.COMPANY_ID=@company_id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_USAGE_STATS
	@company_id as int
as
DECLARE @IMGroups int
DECLARE @IMLogins int
DECLARE @MessSent int
DECLARE @MessReceived int
SELECT @IMLogins = COUNT(*)
  FROM SESSIONS
  WHERE [user_id] IN
	(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
	(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
SELECT @MessSent = COUNT(distinct mess_id)
  FROM USER_MESS
  WHERE from_user_id IN
	(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
	(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
SELECT @MessReceived = COUNT(*)
  FROM USER_MESS
  WHERE to_user_id IN
	(SELECT [user_id] FROM [USER] WHERE imgroup_id IN
	(SELECT imgroup_id FROM IMGROUPS WHERE company_id = @company_id))
	AND to_user_sid IS NOT NULL
SELECT @IMGroups = COUNT(*)
  FROM IMGROUPS
  WHERE company_id = @company_id
SELECT @IMLogins AS IMLogins, @MessSent AS MessSent, @MessReceived AS MessReceived, @IMGroups AS IMGroups
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_USER_IM_SESSIONS
	@user_id as int,
	@fromdate as datetime,
	@todate as datetime,
	@TimeOffset as int
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec = DATEDIFF(s, '1970-01-01 00:00', @fromdate) + @TimeOffset
SET @end_sec = DATEDIFF(s, '1970-01-01 00:00', @todate) + @TimeOffset
select User_Id, DATEADD(s, DT_Begin - @TimeOffset, '1970-01-01 00:00') as SessionBegin, DATEADD(s, DT_End - @TimeOffset, '1970-01-01 00:00') as SessionEnd
from Sessions
where user_id=@User_id and ((dt_begin>=@beg_sec)AND(dt_begin<=@end_sec))
Order by SessionBegin
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_USER_LOGIN_CLIENTS
	@user_id as int,
	@retval as int output
AS
BEGIN
select @retval=count(*) from Sessions where [user_id] = @user_id
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_USER_STATISTIC
	@company_id as int
as
DECLARE @IMGroupCount int
DECLARE @AvgCountUserInIMGroup int
SELECT @IMGroupCount = COUNT(*)
  FROM IMGROUPS
  WHERE company_id = @company_id
SELECT @AvgCountUserInIMGroup = AVG(UserCount)
  FROM
	 (SELECT COUNT(*) AS UserCount
	  FROM IMGROUPS G
		LEFT JOIN [USER] U ON (G.imgroup_id = U.imgroup_id)
	  WHERE company_id = @company_id
	  GROUP BY G.imgroup_id)
	 A
SELECT @IMGroupCount AS IMGroupCount, @AvgCountUserInIMGroup AS AvgCountUserInIMGroup
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].REP_GET_USER_STATS
	@user_id as int,
	@fromdate as datetime,
	@todate as datetime,
	@TimeOffset as int
AS
BEGIN
DECLARE  @beg_sec int, @end_sec int
SET @beg_sec = DATEDIFF(s, '1970-01-01 00:00', @fromdate) + @TimeOffset
SET @end_sec = DATEDIFF(s, '1970-01-01 00:00', @todate) + @TimeOffset
select u_id UserId, lp LoginsPortal, lc LoginsClient, ms MsgSent, mr MsgReceived, cc ConfCreated, cms ConfMsgSent, fs FilesSent, fsb FSBytes, fr FilesReceived, frb FRBytes, pw [Password]
from
(select user_id u_id, [password] pw  from [User] where user_id = @user_id) c0,
(select count(user_id) lp from Portal_sessions where user_id = @user_id and dt_begin between @beg_sec and @end_sec) c1,
(select count(user_id) lc from Sessions where user_id = @user_id and dt_begin between @beg_sec and @end_sec) c2,
(select count(distinct mess_id) ms from user_mess where from_user_id  = @user_id and send_time between @beg_sec and @end_sec) c3,
(select count(distinct mess_id) mr from user_mess where to_user_id  = @user_id and send_time between @beg_sec and @end_sec and from_user_id NOT IN(select user_id FROM [User] where login='alert') ) c4,
(select count(chat_id) cc from chats where owner_id = @user_id and begin_time between @beg_sec and @end_sec) c5,
(select count(distinct mid) cms from chat_mess where user_id = @user_id and send_time between @beg_sec and @end_sec) c6,
(select count(distinct file_id) fs, sum(cast(ISNULL(file_size, 0) as bigint)) fsb from [File] where from_user_id = @user_id and file_date between @beg_sec and @end_sec) c7,
(select count(distinct file_id) fr, sum(cast(ISNULL(file_size, 0) as bigint))  frb from [File] where to_user_id = @user_id and file_date between @beg_sec and @end_sec) c8
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER AUTH_LIST_UPDATE_TRIGGER ON [dbo].[AUTH_LIST]
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE(from_user_id) OR UPDATE(to_user_id)) AND
	(SELECT COUNT(*) FROM inserted I WHERE I.from_user_id IN
		(SELECT [user_id] FROM [User] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
		(SELECT imgroup_id FROM [User] WHERE [user_id] = I.to_user_id)))) ) != @rows
BEGIN
	RAISERROR ('Invalid FROM_USER_ID or TO_USER_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER CHAT_MESS_UPDATE_TRIGGER ON dbo.CHAT_MESS
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF UPDATE([user_id]) AND
	(SELECT COUNT(*) FROM inserted I WHERE I.[user_id] IN
		(SELECT [user_id] FROM [User] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
		(SELECT imgroup_id FROM [User] WHERE [user_id] IN
		(SELECT owner_id FROM Chats WHERE chat_id = I.chat_id)))))) != @rows
BEGIN
	RAISERROR ('Invalid USER_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER CONTACT_LIST_UPDATE_TRIGGER ON [dbo].[CONTACT_LIST]
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE([user_id]) OR UPDATE(cont_user_id)) AND
	(SELECT COUNT(*) FROM inserted I WHERE
		I.[user_id] IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.cont_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.[user_id] IN
		(SELECT [user_id] FROM [User] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
		(SELECT imgroup_id FROM [User] WHERE [user_id] = I.cont_user_id))))
	) != @rows
BEGIN
	RAISERROR ('Invalid USER_ID or CONT_USER_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER DEPENDENCES_UPDATE_TRIGGER ON [dbo].[DEPENDENCES]
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE([imgroup_id]) OR UPDATE(dep_imgroup_id)) AND
	(SELECT COUNT(*) FROM inserted I WHERE I.imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id = I.dep_imgroup_id)))  != @rows
BEGIN
	RAISERROR ('Invalid IMGROUP_ID or DEP_IMGROUP_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER FILE_UPDATE_TRIGGER ON [dbo].[FILE]
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE(from_user_id) OR UPDATE(to_user_id)) AND
	(SELECT COUNT(*) FROM inserted I WHERE
		I.from_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.to_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.from_user_id IN
		(SELECT [user_id] FROM [User] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
		(SELECT imgroup_id FROM [User] WHERE [user_id] = I.to_user_id))))
	) != @rows
BEGIN
	RAISERROR ('Invalid FROM_USER_ID or TO_USER_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER IMGROUPS_UPDATE_TRIGGER ON dbo.IMGROUPS
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE(company_id) OR UPDATE(imgroup_id)) AND EXISTS
	(SELECT login, company_id, COUNT(*)
	  FROM [User] U JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	  GROUP BY login, company_id
	  HAVING COUNT(*) > 1)
BEGIN
	RAISERROR ('Login duplication are prohibited.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER USER_MESS_UPDATE_TRIGGER ON dbo.USER_MESS
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE(from_user_id) OR UPDATE(to_user_id)) AND
	(SELECT COUNT(*) FROM inserted I WHERE
		I.from_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.to_user_id IN (SELECT [user_id] FROM [User] WHERE imgroup_id IN (SELECT imgroup_id FROM IMGROUPS WHERE is_global = 1)) OR
		I.from_user_id IN
		(SELECT [user_id] FROM [User] WHERE imgroup_id IN
		(SELECT imgroup_id FROM IMGROUPS WHERE company_id IN
		(SELECT company_id FROM IMGROUPS WHERE imgroup_id IN
		(SELECT imgroup_id FROM [User] WHERE [user_id] = I.to_user_id))))
	) != @rows
BEGIN
	RAISERROR ('Invalid FROM_USER_ID or TO_USER_ID inserted.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER USER_UPDATE_TRIGGER ON dbo.[USER]
FOR INSERT, UPDATE
AS
DECLARE @rows int
SELECT @rows = @@rowcount
IF @rows = 0
	RETURN
IF (UPDATE(login) OR UPDATE(imgroup_id)) AND EXISTS
	(SELECT login, R.company_id, COUNT(*)
	  FROM [User] U JOIN IMGROUPS R ON (U.imgroup_id = R.imgroup_id)
	  GROUP BY login, R.company_id
	  HAVING COUNT(*) > 1)
BEGIN
	RAISERROR ('Login duplication is prohibited.', 16, 1)
	ROLLBACK TRANSACTION
END
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
