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
Comments (
)
)
Link to this page:
//www.vb-net.com/wanted/message/Procedure.htm
|
|