取原月的计划内容作为计划表目标月的计划内容,取原月的任务内容作为任务表目标月的任务内容

-- 目标:取原月的计划内容作为计划表目标月的计划内容,取原月的任务内容作为任务表目标月的任务内容
-- author:8023
-- date:2009-4-10
-- 输入:目标时间,原时间

go
raiserror('正在创建存储过程 DustPatrol_Copy_PlanTask ....',0,1)
--创建一个存储过程 DustPatrol_Copy_PlanTask
--创建存储过程开始
go
create procedure DustPatrol_Copy_PlanTask
  @NewDate DATETIME,--目标月份
  @OldDate DATETIME --原月份
WITH ENCRYPTION
as
  begin tran  --启动事务
        declare @ERROR int
        set @ERROR=0
        begin
             declare @tempPlan table --创建临时计划表
             (
               [IDs] [int] IDENTITY(1,1) NOT NULL,--备用ID
               [id] int NOT NULL,
               [InspectorID] [int] NULL,
               [InspectorGroupID] [int] NULL,
               [Enabled] [int] NOT NULL   DEFAULT ((0)),
               [CreateUserID] [int] NOT NULL,
               [BeginTime] [datetime] NOT NULL  DEFAULT (getdate()),
               [EndTime] [datetime] NULL,
               [Remark] [varchar](1000) NULL,
               [PlanName] [varchar](50) NULL
              )
             
             declare @olddatestart datetime--原月第一天0点0分
             set @olddatestart =( select CONVERT(DATETIME,CONVERT(VARCHAR(8),@OldDate,120)+'01',120))

             declare @olddateend datetime--原月最后一天23点59分,如取0点0分用:select dateadd(month,1+datediff(month,0,@OldDate),0)-1
             set @olddateend = (select DATEADD(MS,-3,dateadd(month,1+datediff(month,0,@OldDate),1)-1))

             declare @Newdateend datetime--目标月份最后一天23点59分
             set @Newdateend = (select DATEADD(MS,-3,dateadd(month,1+datediff(month,0,@NewDate),1)-1))
             
           
             --将要取出的计划表中的数据插入到临时计划表中
             insert into @tempPlan  
             select [id], [InspectorID],[InspectorGroupID],[Enabled],[CreateUserID],[BeginTime],[EndTime],[Remark],[PlanName]
             FROM dbo.tblDustPatrolPlan
             WHERE [ID] IN(
                SELECT PlanID
                FROM dbo.tblDustPatrolTask
                WHERE (BeginTime BETWEEN @olddatestart
                        AND @olddateend))
             --select * from @tempPlan
 
             SET @ERROR =@ERROR +@@ERROR
                IF (@ERROR <>0) GOTO EXT

             --声明临时计划中的临时字段
             declare @tempID int,
             @tempIDs int,
             @tempInspectorID int,
             @tempInspectorGroupID int,
             @tempEnabled int,
             @tempCreateUserID int,
             @tempBeginTime datetime,
             @tempEndTime datetime,
             @tempRemark varchar(1000),
             @tempPlanName varchar(50),
             @CurrentID int,
             @count INT,--影响行数
             @I int--计数器
             set @I=1
             SET @count=0
             select distinct * FROM @tempPlan  where [ids]=@i   
           
          
             WHILE @@rowcount<>0   
                 begin
                    --查询第一条数据
                     select top 1
                            @tempIDs =[IDs], @tempID =[ID],@tempInspectorID=[InspectorID], @tempInspectorGroupID=[InspectorGroupID],
                            @tempEnabled=[Enabled],@tempCreateUserID=[CreateUserID],@tempBeginTime=[BeginTime],
                            @tempEndTime=[EndTime],@tempEndTime=[EndTime],@tempRemark=[Remark],@tempPlanName=[PlanName]
                      from @tempPlan
                      where [ids]=@i
                      order by [ids]

                     SET @ERROR =@ERROR +@@ERROR
                           IF (@ERROR <>0) GOTO EXT
                     --插入计划
                     print '插入计划'
                     INSERT INTO dbo.tblDustPatrolPlan VALUES
                     (  
                      @tempInspectorID,
                      @tempInspectorGroupID,
                      @tempEnabled,
                      @tempCreateUserID,
                      CASE WHEN
               DAY(@tempBeginTime)>DAY(@Newdateend)
               THEN
                   @Newdateend + CONVERT(char(10),@tempBeginTime,108)--最后一天的年月日+开始时间
                   else
                   CONVERT(DATETIME,CONVERT(CHAR(4),YEAR(@NewDate))+'-'+CONVERT(CHAR(2),MONTH(@NewDate))+'-'+CONVERT(CHAR(3),DAY(@tempBeginTime))+CONVERT(char(10),@tempBeginTime,108))
               END,

    
               CASE WHEN
                       DAY(@tempEndTime)>DAY(@Newdateend)
               THEN
                   @Newdateend + CONVERT(char(10),@tempEndTime,108)--最后一天的年月日+结束时间
               else
                   CONVERT(DATETIME,CONVERT(CHAR(4),YEAR(@NewDate))+'-'+CONVERT(CHAR(2),MONTH(@NewDate))+'-'+CONVERT(CHAR(3),DAY(@tempEndTime))+CONVERT(char(10),@tempEndTime,108))
               END,

                      @tempRemark ,
                      @tempPlanName
                     )
                     set @CurrentID=@@IDENTITY  
                     SET @ERROR =@ERROR +@@ERROR
                         IF (@ERROR <>0) GOTO EXT

             --插入任务
             print '插入任务'

             INSERT INTO dbo.tblDustPatrolTask(PlanID,BeginTime,Periodicity,RepeatInterval,AreaID,[Status],InspectorID,[Name],ActionTime,[Description])
             SELECT IDENT_CURRENT('tblDustPatrolPlan'),
                    CONVERT(DATETIME,CONVERT(CHAR(4),YEAR(@NewDate))+'-'+CONVERT(CHAR(2),MONTH(@NewDate))+'-'+CONVERT(CHAR(3),DAY(BeginTime))+CONVERT(char(10),BeginTime,108)),
                    Periodicity,0,AreaID,0,InspectorID,[Name],null,[Description]
             FROM dbo.tblDustPatrolTask
             WHERE PlanID = @tempIDs and DAY(BeginTime) < DAY(@Newdateend)
             SET @count=@count+@@Rowcount
             PRINT '影响任务表:'+CONVERT(VARCHAR,@count)+'行'
             SET @ERROR =@ERROR +@@ERROR
                         IF (@ERROR <>0) GOTO EXT
                    
                     set   @i=@i+1  --循环变量增1
                     select  * FROM @tempPlan  where [idS]=@i   
                    
                  end
             PRINT '影响计划表:'+CONVERT(VARCHAR,@i-1)+'行'
        end
 
   --异常出口
   EXT:
   
   --判断执行状态
  IF (@ERROR =0)
     BEGIN
         COMMIT
     END
  ELSE
     ROLLBACK
go
--创建存储过程结束
raiserror('创建存储过程结束 DustPatrol_Copy_PlanTask ....',0,1)
--执行存储过程
--exec DustPatrol_Copy_PlanTask '2009-6-11','2008-12-11'