00001: CREATE Function SY_MG
00002: (
00003: @CurrentUser int,
00004: @Sort1 as nvarchar(50),
00005: @Desc1 as bit
00006: )
00007: RETURNS TABLE
00008: AS
00009: RETURN SELECT top 4294967295
00010:
00011: [ID],
00012: dbo.FN_User_GetUserName(SenderID,@CurrentUser) as 'Получатель',
00013: dbo.FN_User_GetUserName(RecipientID,@CurrentUser) as 'Отправитель',
00014: [Text] As 'Текст сообщения',
00015: ToOrder as 'Заказ',
00016: ToMsg as 'К сообщению',
00017: IsConfirm 'Уведомление о получении',
00018: IsConfirmJob as 'Уведомление об исполнении',
00019: IsJob as 'Задача',
00020: SenderComp as 'Комп Отправителя',
00021: SenderDate as 'Дата',
00022: PopUpDelay 'Время всплытия',
00023: ReadDate as 'Дата получения',
00024: ReadNotif as 'Требуется уведомление',
00025: ConfirmNeeded as 'Требуется подтверждение',
00026: JobCompleteNotif 'Требуется исполнение',
00027: ReadComp as 'Комп Получателя',
00028: JobComleteDate as 'Дата исполнения',
00029: JobComleteComp as 'Комп исполнения',
00030: LiveType,LiveTime as 'Время жизни',
00031: sFilename as 'Прилагается файл',
00032: SY_MG_Priority.sName as 'Приоритет',
00033: SY_MG_LiveTimeType.sName as 'Срок жизни',
00034: DeleteTime as 'Дата удаления',
00035: SenderID,
00036: FileContent,
00037: RecipientType,
00038: RecipientID,
00039: Code,
00040: SenderUser.sName as SenderUser_sName ,
00041: SenderUser.sFullName as SenderUser_FullName,
00042: Priority,
00043: RecipientUser.sName as RecipientUser_Name,
00044: RecipientUser.sFullName as RecipientUser_FullName
00045:
00046:
00047: FROM SY_Mg_Log
00048: join SY_Users as SenderUser on SY_Mg_Log.[SenderID] = SenderUser.sID
00049: join SY_Users as RecipientUser on SY_Mg_Log.[RecipientID]= RecipientUser.sID
00050: join SY_MG_Priority on SY_Mg_Log.[Priority] = SY_MG_Priority.sID
00051: join SY_MG_LiveTimeType on SY_Mg_Log.[LiveType] = SY_MG_LiveTimeType.sID
00052: order by
00053: Case When (@Sort1='Priority' or @Sort1='') and @Desc1=1 Then Priority Else Null End Desc,
00054: Case When (@Sort1='Priority' or @Sort1='') and @Desc1=0 Then Priority Else Null End Asc ,
00055: Case When (@Sort1='[ID]' or @Sort1='ID') and @Desc1=1 Then [ID] Else Null End Desc,
00056: Case When (@Sort1='[ID]' or @Sort1='ID') and @Desc1=0 Then [ID] Else Null End Asc ,
00057: Case When @Sort1='Приоритет' and @Desc1=1 Then SY_MG_Priority.sName Else Null End Desc,
00058: Case When @Sort1='Приоритет' and @Desc1=0 Then SY_MG_Priority.sName Else Null End Asc ,
00059: Case When @Sort1='RecipientID' and @Desc1=1 Then RecipientID Else Null End Desc,
00060: Case When @Sort1='RecipientID' and @Desc1=0 Then RecipientID Else Null End Asc ,
00061: Case When @Sort1='SenderID' and @Desc1=1 Then SenderID Else Null End Desc,
00062: Case When @Sort1='SenderID' and @Desc1=0 Then SenderID Else Null End Asc ,
00063: Case When @Sort1='Текст сообщения' and @Desc1=1 Then [Text] Else Null End Desc,
00064: Case When @Sort1='Текст сообщения' and @Desc1=0 Then [Text] Else Null End Asc ,
00065: Case When @Sort1='Заказ' and @Desc1=1 Then ToOrder Else Null End Desc,
00066: Case When @Sort1='Заказ' and @Desc1=0 Then ToOrder Else Null End Asc ,
00067: Case When @Sort1='К сообщению' and @Desc1=1 Then ToMsg Else Null End Desc,
00068: Case When @Sort1='К сообщению' and @Desc1=0 Then ToMsg Else Null End Asc ,
00069: Case When @Sort1='Комп Отправителя' and @Desc1=1 Then SenderComp Else Null End Desc,
00070: Case When @Sort1='Комп Отправителя' and @Desc1=0 Then SenderComp Else Null End Asc ,
00071: Case When @Sort1='Дата' and @Desc1=1 Then SenderDate Else Null End Desc,
00072: Case When @Sort1='Дата' and @Desc1=0 Then SenderDate Else Null End Asc ,
00073: Case When @Sort1='Время всплытия' and @Desc1=1 Then PopUpDelay Else Null End Desc,
00074: Case When @Sort1='Время всплытия' and @Desc1=0 Then PopUpDelay Else Null End Asc ,
00075: Case When @Sort1='Задача' and @Desc1=1 Then IsJob Else Null End Desc,
00076: Case When @Sort1='Задача' and @Desc1=0 Then IsJob Else Null End Asc ,
00077: Case When @Sort1='Требуется уведомление' and @Desc1=1 Then ReadNotif Else Null End Desc,
00078: Case When @Sort1='Требуется уведомление' and @Desc1=0 Then ReadNotif Else Null End Asc ,
00079: Case When @Sort1='Требуется исполнение' and @Desc1=1 Then JobCompleteNotif Else Null End Desc,
00080: Case When @Sort1='Требуется исполнение' and @Desc1=0 Then JobCompleteNotif Else Null End Asc ,
00081: Case When @Sort1='Требуется подтверждение' and @Desc1=1 Then ConfirmNeeded Else Null End Desc,
00082: Case When @Sort1='Требуется подтверждение' and @Desc1=0 Then ConfirmNeeded Else Null End Asc ,
00083: Case When @Sort1='Дата получения' and @Desc1=1 Then ReadDate Else Null End Desc,
00084: Case When @Sort1='Дата получения' and @Desc1=0 Then ReadDate Else Null End Asc ,
00085: Case When @Sort1='Комп Получателя' and @Desc1=1 Then ReadComp Else Null End Desc,
00086: Case When @Sort1='Комп Получателя' and @Desc1=0 Then ReadComp Else Null End Asc ,
00087: Case When @Sort1='Дата исполнения' and @Desc1=1 Then JobComleteDate Else Null End Desc,
00088: Case When @Sort1='Дата исполнения' and @Desc1=0 Then JobComleteDate Else Null End Asc ,
00089: Case When @Sort1='Комп исполнения' and @Desc1=1 Then JobComleteComp Else Null End Desc,
00090: Case When @Sort1='Комп исполнения' and @Desc1=0 Then JobComleteComp Else Null End Asc ,
00091: Case When @Sort1='LiveType' and @Desc1=1 Then LiveType Else Null End Desc,
00092: Case When @Sort1='LiveType' and @Desc1=0 Then LiveType Else Null End Asc ,
00093: Case When @Sort1='Время жизни' and @Desc1=1 Then LiveTime Else Null End Desc,
00094: Case When @Sort1='Время жизни' and @Desc1=0 Then LiveTime Else Null End Asc ,
00095: Case When @Sort1='Прилагается файл' and @Desc1=1 Then sFilename Else Null End Desc,
00096: Case When @Sort1='Прилагается файл' and @Desc1=0 Then sFilename Else Null End Asc ,
00097: Case When @Sort1='Получатель' and @Desc1=1 Then dbo.FN_User_GetUserName(SenderID,@CurrentUser) Else Null End Desc,
00098: Case When @Sort1='Получатель' and @Desc1=0 Then dbo.FN_User_GetUserName(SenderID,@CurrentUser) Else Null End Asc ,
00099: Case When @Sort1='SenderUser.sName' and @Desc1=1 Then SenderUser.sName Else Null End Desc,
00100: Case When @Sort1='SenderUser.sName' and @Desc1=0 Then SenderUser.sName Else Null End Asc ,
00101: Case When @Sort1='SenderUser.sFullName' and @Desc1=1 Then SenderUser.sFullName Else Null End Desc,
00102: Case When @Sort1='SenderUser.sFullName' and @Desc1=0 Then SenderUser.sFullName Else Null End Asc ,
00103: Case When @Sort1='Отправитель' and @Desc1=1 Then dbo.FN_User_GetUserName(RecipientID,@CurrentUser) Else Null End Desc,
00104: Case When @Sort1='Отправитель' and @Desc1=0 Then dbo.FN_User_GetUserName(RecipientID,@CurrentUser) Else Null End Asc ,
00105: Case When @Sort1='RecipientUser.sName' and @Desc1=1 Then RecipientUser.sName Else Null End Desc,
00106: Case When @Sort1='RecipientUser.sName' and @Desc1=0 Then RecipientUser.sName Else Null End Asc ,
00107: Case When @Sort1='RecipientUser.sFullName' and @Desc1=1 Then RecipientUser.sFullName Else Null End Desc,
00108: Case When @Sort1='RecipientUser.sFullName' and @Desc1=0 Then RecipientUser.sFullName Else Null End Asc ,
00109: Case When @Sort1='Срок жизни' and @Desc1=1 Then SY_MG_LiveTimeType.sName Else Null End Desc,
00110: Case When @Sort1='Срок жизни' and @Desc1=0 Then SY_MG_LiveTimeType.sName Else Null End Asc ,
00111: Case When @Sort1='Уведомление о получении' and @Desc1=1 Then IsConfirm Else Null End Desc,
00112: Case When @Sort1='Уведомление о получении' and @Desc1=0 Then IsConfirm Else Null End Asc ,
00113: Case When @Sort1='Уведомление об исполнении'and @Desc1=1 Then IsConfirmJob Else Null End Desc,
00114: Case When @Sort1='Уведомление об исполнении'and @Desc1=0 Then IsConfirmJob Else Null End Asc ,
00115: Case When @Sort1='RecipientType' and @Desc1=1 Then RecipientType Else Null End Desc,
00116: Case When @Sort1='RecipientType' and @Desc1=0 Then RecipientType Else Null End Asc ,
00117: Case When @Sort1='Code' and @Desc1=1 Then Code Else Null End Desc,
00118: Case When @Sort1='Code' and @Desc1=0 Then Code Else Null End Asc ,
00119: Case When @Sort1='Дата удаления' and @Desc1=1 Then DeleteTime Else Null End Desc,
00120: Case When @Sort1='Дата удаления' and @Desc1=0 Then DeleteTime Else Null End Asc
00121:
00122: --------
00123:
00124: CREATE FUNCTION FN_User_LoadVar (@SY_ID int, @NumVar as int, @IfEmpty as nvarchar(50)) RETURNS Nvarchar(50)
00125: as
00126: BEGIN
00127: Declare @Var nvarchar(50)
00128: If not exists(select * from SY_Users where @SY_ID = sID) set @Var= 'Нет такого логина'
00129: If not exists(select * from SY_VarsContainer where svarID=@NumVar and sUserID=@SY_ID)
00130: BEGIN --Default user
00131: -- и для него тоже ничего не указано
00132: If not exists(select * from SY_VarsContainer where svarID=@NumVar and sUserID=1)
00133: set @Var=@ifEmpty
00134: Else
00135: Select @Var=sVar from SY_VarsContainer where svarID=@NumVar and sUserID=1
00136: END
00137: ELSE Select @Var=sVar from SY_VarsContainer where svarID=@NumVar and sUserID=@SY_ID
00138: RETURN @Var
00139: END
00140:
00141: -------
00142:
00143: create procedure SY_MG_JOB_CREATE
00144: as
00145: -- Script generated on 05.11.2005 3:19
00146: -- By: COMPUTER\Администратор
00147: -- Server: COMPUTER
00148:
00149: BEGIN TRANSACTION
00150: DECLARE @JobID BINARY(16)
00151: DECLARE @ReturnCode INT
00152: SELECT @ReturnCode = 0
00153: IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
00154: EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
00155:
00156: -- Delete the job with the same name (if it exists)
00157: SELECT @JobID = job_id
00158: FROM msdb.dbo.sysjobs
00159: WHERE (name = N'Очистка устаревших почтовых сообщений')
00160: IF (@JobID IS NOT NULL)
00161: BEGIN
00162: -- Check if the job is a multi-server job
00163: IF (EXISTS (SELECT *
00164: FROM msdb.dbo.sysjobservers
00165: WHERE (job_id = @JobID) AND (server_id <> 0)))
00166: BEGIN
00167: -- There is, so abort the script
00168: RAISERROR (N'Unable to import job ''Очистка устаревших почтовых сообщений'' since there is already a multi-server job with this name.', 16, 1)
00169: GOTO QuitWithRollback
00170: END
00171: ELSE
00172: -- Delete the [local] job
00173: EXECUTE msdb.dbo.sp_delete_job @job_name = N'Очистка устаревших почтовых сообщений'
00174: SELECT @JobID = NULL
00175: END
00176:
00177: BEGIN
00178:
00179: -- Add the job
00180: EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Очистка устаревших почтовых сообщений', @owner_login_name = N'COMPUTER\Администратор', @description = N'Очистка устаревших сообщений', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
00181: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
00182:
00183: -- Add the job steps
00184: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Cleaner', @command = N'exec SY_MG_MessageDelete', @database_name = N'sva_cur', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
00185: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
00186: EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
00187:
00188: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
00189:
00190: -- Add the job schedules
00191: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Ежечасно', @enabled = 1, @freq_type = 4, @active_start_date = 20051105, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
00192: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
00193:
00194: -- Add the Target Servers
00195: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
00196: IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
00197:
00198: END
00199: COMMIT TRANSACTION
00200: GOTO EndSave
00201: QuitWithRollback:
00202: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
00203: EndSave:
00204:
00205: ----------
00206:
00207: CREATE procedure SY_MG_ReplayRobot
00208: @ID int,
00209: @ComputerName nvarchar(50),
00210: @CurrentUser int,
00211: @Confirm int,
00212: @Comlpete int
00213: as
00214: If (@Confirm=0 and @Comlpete=0) return(1)
00215: Declare @Notif as bit, @SenderID int, @Txt nvarchar(100),@Dt as Datetime,
00216: @JobCompleteNotif as bit, @SenderName as nvarchar(50),@iLivetime int ,
00217: @iLiveTimeType int, @xMessage as nvarchar(250), @Ret int, @IsConfirm bit, @IsConfirmJob bit
00218:
00219: -- взяли все что надо для сообщения с записи, требующей подтверждения
00220: select @Notif=[Требуется уведомление], @SenderID=[SenderID],@Txt=Left([Текст сообщения],100),@Dt=[Дата],
00221: @SenderName=[Отправитель], @iLivetime=[Время жизни], @iLiveTimeType=[LiveType], @JobCompleteNotif= [Требуется исполнение]
00222: from SY_MG(@CurrentUser,'',0) where [ID]=@ID and @CurrentUser=[RecipientID]
00223:
00224: --@Confirm=1 - сообщение только что прочитано
00225: --@Comlpete=1 - только что подтверждено выполнение задачи
00226: --@Notif=1 - требуется подтвержение о прочтении
00227: --@JobCompleteNotif=1 - требуется подтверждение об исполнении
00228:
00229: If (@Confirm=1 and @Notif=1)
00230: Select @IsConfirm =1, @IsConfirmJob=0,
00231: @xMessage= 'Сообщение ('+cast(@ID as nvarchar(10))+') "'+@Txt+'..." , отправленное '+ cast(@Dt as nvarchar(30)) + ', было прочитано пользователем "'+@SenderName+ '" на компьютере "'+@ComputerName +'" '+cast(Getdate() as nvarchar(30))
00232:
00233: else if (@Comlpete=1 and @JobCompleteNotif=1)
00234: Select @IsConfirm =0, @IsConfirmJob=1,
00235: @xMessage= 'Задача ('+cast(@ID as nvarchar(10))+') "'+@Txt+'..." , отправленная '+ cast(@Dt as nvarchar(30)) + ', была исполнена пользователем "'+@SenderName+ cast(Getdate() as nvarchar(30))
00236:
00237: else if (@Confirm=1 and @Notif=1 and @Comlpete=1 and @JobCompleteNotif=1)
00238: Select @IsConfirm =1, @IsConfirmJob=1,
00239: @xMessage= 'Сообщение ('+cast(@ID as nvarchar(10))+') "'+@Txt+'..." , отправленное '+ cast(@Dt as nvarchar(30)) + ', было прочитано пользователем "'+@SenderName+ '" на компьютере "'+@ComputerName +'" '+cast(Getdate() as nvarchar(30))+ ' и задача отмечена как исполненная.'
00240:
00241: else return(1)
00242:
00243: EXEC @Ret= [SY_MG_MessageWrite] @ID= @SenderID,@CurrentUser=@CurrentUser, @MachineName=@ComputerName,
00244: @txMessage =@xMessage, @lNumZakaz =0, @sFilename =NULL, @FileContent=Default,
00245: @iPriority =1, @iLivetime =@iLivetime, @iLiveTimeType =@iLiveTimeType,
00246: @iPopUpTime =0, @IsTask =0, @IsTaskReplay =0, @IsAccept =0,@IsConfirmNeeded =0,
00247: @ToMsg =@ID, @RecipientType=0,@IsConfirm =@IsConfirm,@IsConfirmJob =@IsConfirmJob
00248:
00249:
00250: Return (@Ret)
00251:
00252: ----------
00253:
00254: CREATE procedure SY_MG_ProfileWrite
00255: @sName nvarchar (50),
00256: @CurrentUser smallint ,
00257: @Priority int,
00258: @LiveTime int,
00259: @LiveTimeType int,
00260: @PopUP int,
00261: @IsTask bit,
00262: @IsTaskReplay bit,
00263: @IsAccept bit,
00264: @IsConfirm bit
00265: as
00266: If not exists(select * from SY_MG_Profile where [sName]=@sName)
00267: INSERT INTO SY_MG_Profile ([sName], [CurrentUser], [Priority], [LiveTime], [LiveTimeType], [PopUP], [IsTask], [IsTaskReplay], [IsAccept], [IsConfirm])
00268: VALUES(@sName,@CurrentUser,@Priority,@LiveTime,@LiveTimeType,@PopUP,@IsTask,@IsTaskReplay,@IsAccept,@IsConfirm)
00269: else
00270: UPDATE SY_MG_Profile
00271: SET [Priority]=@Priority, [LiveTime]=@LiveTime, [LiveTimeType]=@LiveTimeType, [PopUP]=@PopUP, [IsTask]=@IsTask, [IsTaskReplay]=@IsTaskReplay, [IsAccept]=@IsAccept, [IsConfirm]=@IsConfirm
00272: WHERE [sName]=@sName
00273: If @@rowcount=1 return(0)
00274: else return(1)
00275:
00276: ----------
00277:
00278: CREATE procedure SY_MG_ProfileRead
00279: @CurrentUser smallint,
00280: @sName nvarchar(50)
00281: as
00282: select sName,Priority,LiveTime,LiveTimeType,PopUP,IsTask,IsTaskReplay,IsAccept,IsConfirm
00283: from SY_MG_Profile
00284: where CurrentUser=@CurrentUser and sName=@sName
00285:
00286: -----------
00287:
00288: create procedure SY_MG_ProfileDelete
00289: @sName as nvarchar(50)
00290: as
00291: DELETE FROM SY_MG_Profile
00292: WHERE sName=@sName
00293: If @@RowCount=1 return(0)
00294: else return(1)
00295:
00296: -------------
00297:
00298: create procedure SY_MG_ProfileCheck
00299: @CurrentUser smallint ,
00300: @Priority int,
00301: @LiveTime int,
00302: @LiveTimeType int,
00303: @PopUP int,
00304: @IsTask bit,
00305: @IsTaskReplay bit,
00306: @IsAccept bit,
00307: @IsConfirm bit
00308: as
00309: select sName from SY_MG_Profile
00310: where [CurrentUser]=@CurrentUser and [Priority]=@Priority and [LiveTime]=@LiveTime and [LiveTimeType]=@LiveTimeType and
00311: [PopUP]=@PopUP and [IsTask]= @IsTask and [IsTaskReplay]=@IsTaskReplay and [IsAccept]=@IsAccept and [IsConfirm]=@IsConfirm
00312:
00313: -----------
00314:
00315: CREATE procedure SY_MG_MessageWrite
00316: @ID smallint,
00317: @CurrentUser int,
00318: @MachineName nvarchar(50),
00319: @txMessage nvarchar(3500),
00320: @lNumZakaz int,
00321: @sFilename nvarchar(256),
00322: @FileContent ntext = NULL,
00323: @iPriority int,
00324: @iLivetime int,
00325: @iLiveTimeType int,
00326: @iPopUpTime int,
00327: @IsTask bit,
00328: @IsTaskReplay bit,
00329: @IsAccept bit,
00330: @IsConfirmNeeded bit,
00331: @ToMsg int,
00332: @RecipientType int,
00333: @IsConfirm bit,
00334: @IsConfirmJob bit
00335: as
00336:
00337:
00338: INSERT SY_Mg_Log([RecipientType], [RecipientID],
00339: [Code], [Text],[ToOrder], [ToMsg], [SenderID], [SenderComp], [SenderDate],
00340: [PopUpDelay], [Priority], [IsJob], [ReadNotif], [JobCompleteNotif], [ConfirmNeeded],
00341: [ReadDate], [ReadComp], [JobComleteDate], [JobComleteComp],
00342: [LiveType], [LiveTime],[sFilename],[FileContent],[IsConfirm],[IsConfirmJob])
00343: VALUES(@RecipientType,@ID,
00344: 0, @txMessage, @lNumZakaz , @ToMsg, @CurrentUser, @MachineName, Getdate(),
00345: @iPopUpTime, @iPriority ,@IsTask ,@IsAccept , @IsTaskReplay , @IsConfirmNeeded,
00346: NULL, NULL, NULL, NULL,
00347: @iLiveTimeType, @iLivetime,@sFilename,@FileContent,@IsConfirm,@IsConfirmJob )
00348:
00349: Declare @KolInsert as int
00350: set @KolInsert=@@Rowcount
00351: If @KolInsert<>1 Begin
00352: RAISERROR ('Ошибка. SY_MG_MessageWrite не записал сообщение.',16,1)
00353: Return(1)
00354: End
00355:
00356: If @iLiveTimeType is NULL Return(2)
00357: --без этого вся дальнейшая работа этой процедуры бессмысленна
00358:
00359: Declare @LiveTipe int,@LiveTime int, @SenderDate datetime
00360: select @LiveTipe=[LiveType], @LiveTime=[LiveTime],@SenderDate=[SenderDate]
00361: from SY_Mg_Log where [ID]=scope_identity()
00362:
00363:
00364: Declare @IsDay bit, @IsMinute bit
00365: select @IsDay=[IsDay], @IsMinute=[IsMinute] from SY_MG_LiveTimeType where [sID]=@LiveTipe
00366:
00367: If (@IsDay=0 and @IsMinute=0) return(6) --такой срок жизни сообщений обрабатывается иначе
00368: else if (@IsDay is Null and @IsMinute is NULL) begin
00369: RAISERROR ('Ошибка. Неверная структура таблицы SY_MG_LiveTimeType',16,1)
00370: Return(3)
00371: end
00372: else if (@IsDay=1 and @IsMinute=0) Begin
00373: -- срок жизни указан в днях
00374: update SY_Mg_Log set [DeleteTime]=DATEADD(d, @LiveTime,@SenderDate)
00375: where [ID]=scope_identity()
00376: IF @@Rowcount<>1 Return(4)
00377: end
00378: else if (@IsDay =0 and @IsMinute=1) Begin
00379: -- срок жизни указан в минутах
00380: update SY_Mg_Log set [DeleteTime]=DATEADD(n, @LiveTime,@SenderDate)
00381: where [ID]=scope_identity()
00382: IF @@Rowcount<>1 return(5)
00383: end
00384:
00385: return(0)
00386:
00387: --------
00388:
00389: CREATE procedure SY_MG_MessageReadToolbar3
00390: @CurrentUser int =0
00391: as
00392: Declare @Sort as nvarchar(50),@Desc bit
00393: select @Sort='',@Desc=0
00394: select *
00395: from SY_MG(@CurrentUser,@Sort,@Desc)
00396: where RecipientID=@CurrentUser and [Комп получателя] is null
00397:
00398: -------------
00399:
00400: CREATE procedure SY_MG_MessageReadToolbar
00401:
00402: @CurrentUser int =0,
00403: @Recipient as bit=1, -- отбор где CurrentUser отправитель
00404: @Sender as bit=1, -- отбор где CurrentUser получатель
00405: @JobsOnly as bit=0, -- доп фильтр только задач
00406: @JobsOnlyNotConfirmed as bit= 0 -- доп фильтр невыполненных задач
00407: as
00408:
00409: Declare @Sort as nvarchar(50),@Desc bit
00410: select @Sort='',@Desc=0
00411:
00412: select *
00413: from SY_MG(@CurrentUser,@Sort,@Desc)
00414: where (@JobsOnly=1 or [Задача]=1)
00415: and (@JobsOnlyNotConfirmed=1 or [Уведомление об исполнении] is NULL)
00416: and (@Recipient=1 or RecipientID=@CurrentUser)
00417: and (@Sender=1 or SenderID=@CurrentUser)
00418:
00419: ------------
00420:
00421: CREATE procedure SY_MG_MessageReadOne
00422: @ID int,
00423: @CurrentUser int = 0
00424: as
00425: select * from SY_MG(@CurrentUser,'',0) where [ID]=@ID
00426:
00427: ------------
00428:
00429: CREATE procedure SY_MG_MessageReadAll
00430: @CurrentUser int =0,
00431: @Recipient as bit=1, -- отбор где CurrentUser отправитель
00432: @Sender as bit=1, -- отбор где CurrentUser получатель
00433: @JobsOnly as bit=0, -- доп фильтр только задач
00434: @JobsOnlyNotConfirmed as bit= 0, -- доп фильтр невыполненных задач
00435: @Sort as nvarchar(50)='', -- порядок сортировки
00436: @Desc as bit=1, -- доп порядок сортировки ASC/DESC
00437: @Date1 as datetime ='01-01-1900', -- доп фильтр по датам - начальная
00438: @Date2 as datetime ='01-01-2100' -- конечная
00439:
00440: as
00441: select *
00442: from SY_MG(@CurrentUser,@Sort,@Desc)
00443: where([Дата] between @Date1 and @Date2)
00444: and ((@Recipient=0 or RecipientID=@CurrentUser)
00445: or (@Sender=0 or SenderID=@CurrentUser))
00446: and (@JobsOnly=0 or [Задача]=1)
00447: and (@JobsOnlyNotConfirmed=0 or [Уведомление об исполнении] is NULL)
00448:
00449: ---------
00450:
00451: CREATE procedure SY_MG_MessageReadAdmin5
00452: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00453: @Sort as nvarchar(50)='', -- порядок сортировки
00454: @Desc as bit=1, -- доп порядок сортировки ASC/DESC
00455: @Zakaz as bigint = 0
00456: as
00457:
00458: select *
00459: from SY_MG(@CurrentUser,@Sort,@Desc)
00460: where @Zakaz=[Заказ] and [Заказ]>0
00461:
00462: ------------
00463:
00464: CREATE procedure SY_MG_MessageReadAdmin4
00465: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00466: @Sort as nvarchar(50)='', -- порядок сортировки
00467: @Desc as bit=1 -- доп порядок сортировки ASC/DESC
00468: as
00469:
00470: select *
00471: from SY_MG(@CurrentUser,@Sort,@Desc)
00472: where SenderID=@CurrentUser and [Комп Получателя] is Null and [Требуется подтверждение]=1
00473:
00474: ------------
00475:
00476: CREATE procedure SY_MG_MessageReadAdmin3
00477: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00478: @Sort as nvarchar(50)='', -- порядок сортировки
00479: @Desc as bit=1 -- доп порядок сортировки ASC/DESC
00480: as
00481:
00482: select *
00483: from SY_MG(@CurrentUser,@Sort,@Desc)
00484: where SenderID=@CurrentUser and [Комп Получателя] is Null
00485:
00486: --------------
00487:
00488: CREATE procedure SY_MG_MessageReadAdmin2
00489: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00490: @Sort as nvarchar(50)='', -- порядок сортировки
00491: @Desc as bit=1, -- доп порядок сортировки ASC/DESC
00492: @Date1 as datetime ='01-01-1900', -- доп фильтр по датам - начальная
00493: @Date2 as datetime ='01-01-2100', -- конечная
00494: @JobsOnlyNotConfirmed as bit= 0 -- доп фильтр невыполненных задач
00495: as
00496:
00497: select *
00498: from SY_MG(@CurrentUser,@Sort,@Desc)
00499: where (SenderID=@CurrentUser) and ([Задача]=1)
00500: and (@JobsOnlyNotConfirmed=1 or ([Дата] between @Date1 and @Date2))
00501: and (@JobsOnlyNotConfirmed=0 or ([Комп исполнения] is Null))
00502:
00503: ------------
00504:
00505: CREATE procedure SY_MG_MessageReadAdmin1
00506: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00507: @Sort as nvarchar(50)='', -- порядок сортировки
00508: @Desc as bit=1, -- доп порядок сортировки ASC/DESC
00509: @Date1 as datetime ='01-01-1900', -- доп фильтр по датам - начальная
00510: @Date2 as datetime ='01-01-2100', -- конечная
00511: @JobsOnlyNotConfirmed as bit= 0 -- доп фильтр невыполненных задач
00512: as
00513:
00514: select *
00515: from SY_MG(@CurrentUser,@Sort,@Desc)
00516: where (RecipientID=@CurrentUser) and ([Задача]=1)
00517: and (@JobsOnlyNotConfirmed=1 or ([Дата] between @Date1 and @Date2))
00518: and (@JobsOnlyNotConfirmed=0 or ([Комп исполнения] is Null))
00519:
00520:
00521: ------------------
00522:
00523: CREATE procedure SY_MG_MessageReadAdmin
00524: @CurrentUser int =0, -- отбор - получатель и отправитель CurrentUser
00525: @AdminUser as Int=0, -- опция админа (от кого переписка)
00526: @SetUser as int=0, -- с кем переписка
00527: @Sort1 as nvarchar(50)='', -- порядок сортировки
00528: @Desc1 as bit=1, -- доп порядок сортировки ASC/DESC
00529: @Date1 as datetime ='01-01-1900', -- доп фильтр по датам - начальная
00530: @Date2 as datetime ='01-01-2100', -- конечная
00531: @Sort2 as nvarchar(50)='', -- порядок сортировки
00532: @Desc2 as bit=1, -- доп порядок сортировки ASC/DESC
00533: @JobsOnly as bit=0 -- доп фильтр только задач
00534: as
00535:
00536: Declare @TableName as Nvarchar(50), @Select1 as Nvarchar(300)
00537: Select @TableName='SY_MG_TempTable_'+Replace(cast(newid() as nvarchar(36)),'-','_')
00538: select @Select1='select * Into '+@TableName+
00539: ' from SY_MG('+cast(@CurrentUser as nvarchar(5))+','''+@Sort1+''','+cast(@Desc1 as nvarchar(1))+')'+
00540: ' where([Дата] between '''+cast(@Date1 as nvarchar(50))+''' and '''+cast(@Date2 as nvarchar(50))+''')'+
00541: ' and ('+cast(@JobsOnly as nvarchar(1))+'=0 or [Задача]=1)'
00542:
00543: --print @Select1
00544: exec(@Select1)
00545:
00546: Declare @Desc1_txt nchar(6), @Desc2_txt nchar(6), @Sort1_txt nchar(50), @Str1 nvarchar(1000)
00547: If @Desc1=1 set @Desc1_txt=' Desc '
00548: else set @Desc1_txt=' Asc '
00549: If @Desc2=1 set @Desc2_txt=' Desc '
00550: else set @Desc2_txt=' Asc '
00551: If @Sort1='' set @Sort1_txt='ID'
00552: else set @Sort1_txt=@Sort1
00553:
00554: Declare @Where nvarchar(100)
00555: If (@SetUser=0 and @AdminUser=0) set @Where=''
00556: else if (@SetUser<>0 and @AdminUser<>0) set @Where=' Where RecipientID=' +Cast(@SetUser as nvarchar(5)) + ' or SenderID=' + cast(@SetUser as nvarchar(5))+ ' or RecipientID='+ cast(@AdminUser as nvarchar(5))+ ' or SenderID='+ cast(@AdminUser as nvarchar(5))
00557: else If (@SetUser=0 and @AdminUser<>0) set @Where=' Where RecipientID=' +cast(@AdminUser as nvarchar(5))+ ' or SenderID=' + cast(@AdminUser as nvarchar(5))
00558: else If (@SetUser<>0 and @AdminUser=0) set @Where=' Where RecipientID=' +Cast(@SetUser as nvarchar(5))+ ' or SenderID=' + Cast(@SetUser as nvarchar(5))
00559:
00560: If @Sort1<>@Sort2 set @Str1=@str1 + ',[' + @Sort2+ ']' + @Desc2_txt
00561:
00562: set @Str1='select * from '+@TableName+' '+ @Where +' order by ['+ @Sort1_txt + ']' + @Desc1_txt
00563:
00564:
00565: --raiserror( @str2,1,1)
00566: exec (@Str1)
00567:
00568: IF EXISTS(SELECT name
00569: FROM sysobjects
00570: WHERE name = @TableName
00571: AND type = 'U')
00572: exec('DROP TABLE '+@TableName)
00573:
00574: Return(0)
00575:
00576: ----------------
00577:
00578:
00579: CREATE procedure SY_MG_MessageDeleteSeans
00580: @CurrentUser as int
00581: as
00582: delete from SY_Mg_Log
00583: where SenderID=@CurrentUser
00584: and exists(select [ID] from SY_MG_LiveTimeType
00585: where SY_MG_LOG.LiveType=SY_MG_LiveTimeType.sID and IsSeans=1)
00586:
00587: -----------------
00588:
00589:
00590: Create procedure SY_MG_MessageDelete
00591: as
00592: delete from SY_Mg_Log
00593: where DeleteTime<Getdate()
00594:
00595: ----------------
00596:
00597: CREATE procedure SY_MG_MessageConfirm
00598: @ID int,
00599: @ComputerName nvarchar(50),
00600: @CurrentUser int,
00601: @JobComlpete bit
00602: as
00603: --
00604: --Отметка о прочтении
00605: --
00606: UPDATE SY_Mg_Log
00607: SET [ReadComp]=@ComputerName, [ReadDate]=Getdate()
00608: WHERE [ID]=@ID and @CurrentUser=[RecipientID] and [ReadComp] is NULL
00609: Declare @Confirm int
00610: set @Confirm=@@Rowcount
00611:
00612: --
00613: --Отметка об исполнении
00614: --
00615: Declare @Comlpete int
00616: set @Comlpete=0
00617: If @JobComlpete=1 BEGIN
00618: UPDATE SY_Mg_Log
00619: SET [JobComleteComp]=@ComputerName, [JobComleteDate]=Getdate()
00620: WHERE [ID]=@ID and @CurrentUser=[RecipientID] and [JobComleteComp] is NULL
00621: set @Comlpete=@@Rowcount
00622: END
00623: --
00624: --Теперь посылаем уведомление о прочтении
00625: --
00626: Declare @Ret int,@RetCode int
00627: EXEC @Ret=SY_MG_ReplayRobot @ID,@ComputerName,@CurrentUser,@Confirm,@Comlpete
00628:
00629: set @RetCode=@Confirm*1 + @Comlpete*2 + @Ret*4
00630: Return (@RetCode)
00631: --@Ret = 0 при нормальной отправке одного сообщения, иначе 1
00632: --@Confirm=1 при постановке отметки о прочтении
00633: --@Comlpete=1 при постановке отметки об исполнении
00634:
00635:
00636: -------------------
00637:
00638: create procedure SY_MG_DeleteJob
00639: @ID int,
00640: @Full bit
00641: as
00642: If @Full=1
00643: delete from SY_MG_Log where @ID=[ID]
00644: else
00645: UPDATE SY_MG_Log
00646: SET [JobComleteComp]='ОТМЕНЕНО', [JobComleteDate]=getdate()
00647: where @ID=[ID]
00648:
00649: ---------------
00650:
00651: CREATE procedure SY_MG_User
00652: @CurrentUser int = 1
00653: as
00654: select SY_Users.sid,dbo.FN_User_GetUserName(SY_Users.sid,@CurrentUser) as 'Name' from SY_Users where sStatus=1
