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 ( )