lyfz_repos/经典版源码/document/数据库维护脚本/修改订单日期给展会使用.sql

use db
/*
-- 声明变量;
declare @bYear bit							-- 是否修改年份;
declare @bMonth bit							-- 是否修改月份;
declare @nYear int							-- 要修改的年份(所有年加nYear个年);
declare @nMonth int							-- 要修改的月份(所有月加nMonth个月);
declare @StartDate Datetime					-- 开始日期;
declare @EndofDate Datetime					-- 结束日期;
declare @StrStartDate nvarchar(50)			-- 字符串日期;
declare @StrEndofDate nvarchar(50)			-- 字符串日期;
declare @OrdStartDate nvarchar(50)			-- 订单开始;
declare @OrdEndofDate nvarchar(50)			-- 订单结束;
declare @StrTempStartDate nvarchar(50)		-- 临时开始日期;
declare @StrTempEndofDate nvarchar(50)		-- 临时结束日期;
declare @StartTempDate Datetime				-- 临时开始日期;
declare @EndofTempDate Datetime				-- 临时结束日期;
declare @curso cursor						-- 游标;
declare @ord_num nvarchar(50)				-- 订单号;

-- 初始化变量值;
Set @bYear = 1
Set @bMonth = 1
Set @nYear = 1
Set @nMonth = 1
Set @StartDate = '2016-01-01'
Set @EndofDate = '2016-05-01'

-- 将日期转换成日期字符串;
Set @StrStartDate = CONVERT(nvarchar(50), @StartDate, 23)	print @StrStartDate
Set @StrEndofDate = CONVERT(nvarchar(50), @EndofDate, 23)	print @StrEndofDate

Set @OrdStartDate = CONVERT(nvarchar(50), @StartDate, 112)	print @OrdStartDate
Set @OrdEndofDate = CONVERT(nvarchar(50), @EndofDate, 112)	print @OrdEndofDate

-- 查询出所有符合要求的记录;
select * into #temp_client from client where id >= @OrdStartDate and id <= @OrdEndofDate  
delete from client where id >= @OrdStartDate and id <= @OrdEndofDate
if @bYear = 'true'
	begin
		-- 修改订单号;
		Set @StartTempDate = dateadd(m,12*@nYear,@StartDate)	print @StartTempDate
		Set @EndofTempDate = dateadd(m,12*@nYear,@EndofDate)	print @EndofTempDate
		update #temp_client set id = replace(id, year(@StartDate), year(@StartTempDate)), time3 = replace(time3, year(@StartDate), year(@StartTempDate))
	end

if @bMonth = 'true'
	begin
		if @bYear = 'true'
			begin
				Set @StartTempDate = dateadd(m,@nMonth + 12*@nYear,@StartDate)	print @StartTempDate
				Set @EndofTempDate = dateadd(m,@nMonth + 12*@nYear,@EndofDate)	print @EndofTempDate
			end
		else
			begin
				Set @StartTempDate = dateadd(m,@nMonth,@StartDate)	print @StartTempDate
				Set @EndofTempDate = dateadd(m,@nMonth,@EndofDate)	print @EndofTempDate
			end
		update #temp_client set id = replace(id, year(@StartDate) + '-' + month(@StartDate), year(@StartTempDate) + '-' + month(@StartTempDate)), time3 = replace(time3, year(@StartDate), year(@StartTempDate))
	end

select * into #temp_dindan from dindan where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindan where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindanjd from dindanjd where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindanjd where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindansp from dindansp where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindansp where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindansp2 from dindansp2 where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindansp2 where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindansp3 from dindansp3 where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindansp3 where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindanbukuan from dindanbukuan where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindanbukuan where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_dindanbukuan2 from dindanbukuan2 where id >= @OrdStartDate and id <= @OrdEndofDate
delete from dindanbukuan2 where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_mywork from mywork where id >= @OrdStartDate and id <= @OrdEndofDate
delete from mywork where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_burncdreg from burncdreg where id >= @OrdStartDate and id <= @OrdEndofDate
delete from burncdreg where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_photoprint from photoprint where id >= @OrdStartDate and id <= @OrdEndofDate
delete from photoprint where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_digitalwork from digitalwork where id >= @OrdStartDate and id <= @OrdEndofDate
delete from digitalwork where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_singleincome from singleincome where id >= @OrdStartDate and id <= @OrdEndofDate
delete from singleincome where id >= @OrdStartDate and id <= @OrdEndofDate

select * into #temp_singleincomemoney from singleincomemoney where id >= @OrdStartDate and id <= @OrdEndofDate
delete from singleincomemoney where id >= @OrdStartDate and id <= @OrdEndofDate

select * from #temp_dindan order by id


-- 删除所有临时表;
drop table #temp_client
drop table #temp_dindan
drop table #temp_dindanjd
drop table #temp_dindansp
drop table #temp_dindansp2
drop table #temp_dindansp3
drop table #temp_dindanbukuan
drop table #temp_dindanbukuan2
drop table #temp_mywork
drop table #temp_burncdreg
drop table #temp_photoprint
drop table #temp_digitalwork
drop table #temp_singleincome
drop table #temp_singleincomemoney

*/
---------------------------------
use db

-- 声明变量;
declare @bYear bit							-- 是否修改年份;
declare @bMonth bit							-- 是否修改月份;
declare @nYear int							-- 要修改的年份(所有年加nYear个年);
declare @nMonth int							-- 要修改的月份(所有月加nMonth个月);
declare @StartDate Datetime					-- 开始日期;
declare @EndofDate Datetime					-- 结束日期;
declare @StrStartDate nvarchar(50)			-- 字符串日期;
declare @StrEndofDate nvarchar(50)			-- 字符串日期;
declare @OrdStartDate nvarchar(50)			-- 订单开始;
declare @OrdEndofDate nvarchar(50)			-- 订单结束;
declare @StrTempStartDate nvarchar(50)		-- 临时开始日期;
declare @StrTempEndofDate nvarchar(50)		-- 临时结束日期;
declare @StartTempDate Datetime				-- 临时开始日期;
declare @EndofTempDate Datetime				-- 临时结束日期;
--declare @cursor cursor						-- 游标;
declare @ord_num nvarchar(50)				-- 订单号;
declare @ord_year nvarchar(50)				-- 订单年;
declare @ord_month nvarchar(50)				-- 订单月;
declare @ord_day nvarchar(50)				-- 订单日;
declare @ord_temp nvarchar(50)				-- 临时订单;

-- 初始化变量值;
Set @bYear = 1
Set @bMonth = 1
Set @nYear = 1
Set @nMonth = 1
Set @StartDate = '2016-01-01'
Set @EndofDate = '2016-05-01'

-- 将日期转换成日期字符串;
Set @StrStartDate = CONVERT(nvarchar(50), @StartDate, 23)	--print @StrStartDate
Set @StrEndofDate = CONVERT(nvarchar(50), @EndofDate, 23)	--print @StrEndofDate

Set @OrdStartDate = CONVERT(nvarchar(50), @StartDate, 112)	--print @OrdStartDate
Set @OrdEndofDate = CONVERT(nvarchar(50), @EndofDate, 112)	--print @OrdEndofDate

-- 查询出所有符合要求的记录;
select * into #temp_client from client where id >= @OrdStartDate and id <= @OrdEndofDate  
--delete from client where id >= @OrdStartDate and id <= @OrdEndofDate

print ''
print 'goto year'
if @bYear = 'true' and @bMonth = 'true' 
	begin
		-- 修改订单号;
		Set @StartTempDate = dateadd(m,@nMonth + 12*@nYear,@StartDate)	--print @StartTempDate
		Set @EndofTempDate = dateadd(m,@nMonth + 12*@nYear,@EndofDate)	--print @EndofTempDate

		declare cursor1 cursor for select id from client order by id desc 
		-- 打开游标;
		open cursor1			
		-- 读取第一条数据,并将id存入@ord_num中;
		fetch next from cursor1 into @ord_num
		while @@FETCH_STATUS = 0
		begin
			print 'ord:' + @ord_num
			-- 订单号前4位,中2位,后2位;
			select @ord_year = left(@ord_num,4) print @ord_year
			select @ord_month = substring(@ord_num, 5, 2) print @ord_month
			select @ord_day = substring(@ord_num, 7, 2) print @ord_day
			Set @ord_temp = @ord_year + convert(nvarchar, convert(int,@ord_month) + @nMonth) + @ord_day print @ord_temp
			update #temp_client set id = replace(id, year(@StartDate), year(@StartTempDate)), time3 = replace(time3, year(@StartDate), year(@StartTempDate)) where id = @ord_num
			fetch next from cursor1 into @ord_num
		end
		close cursor1 
	end
else if @bYear = 'true' 
	begin
		print 'a'
	end
else if @bMonth = 'true' 
	begin
		print 'a'
	end

select * from #temp_client
drop table #temp_client

--select * from client order by id desc