-- デモの実施日を指定する。
set @demo_date = '2024-09-04';

-- 納品書データの最大日付を取得し、デモ実施日との日数の差を計算する。
select max( dsi.create_date ) into @max_date from invoice.delivery_search_index dsi;
select datediff(@demo_date,@max_date) into @day_diff;
set @intv_minus = -1000;
set @intv_plus = 1000 + @day_diff;

-- お知らせの更新
update invoice.news set open_date = @demo_date where news_id = 83;

-- 納品書検索データの更新
update invoice.delivery_search_index set
    create_date = date_add(create_date, interval @intv_minus day)
    ,create_time = date_add(create_time, interval @intv_minus day);
update invoice.delivery_search_index set
    create_date = date_add(create_date, interval @intv_plus day)
    ,create_time = date_add(create_time, interval @intv_plus day);

-- 納品書日付索引の更新
update invoice.delivery_date_index set
	create_date = date_add(create_date, interval @intv_minus day);
update invoice.delivery_date_index set
	create_date = date_add(create_date, interval @intv_plus day);

-- 納品書ダウンロード履歴のクリアと初期設定
truncate table invoice.download_history_delivery;
insert into invoice.download_history_delivery
(download_history_delivery_id, delivery_id, create_date, customer_code, member_id, admin_id, download_date, create_time)
select 0, 0, dsi.create_date, dsi.customer_code, 1373, 0, date_add(dsi.create_date, interval 1 day), dsi.create_time 
from invoice.delivery_search_index dsi 
where dsi.create_date < @demo_date;

-- 請求書検索データの更新
update invoice.invoice_search_index set
    create_date = date_add(create_date, interval @intv_minus day)
    ,create_time = date_add(create_time, interval @intv_minus day);
update invoice.invoice_search_index set
    create_date = date_add(create_date, interval @intv_plus day)
    ,create_time = date_add(create_time, interval @intv_plus day);

-- 請求書日付索引の更新
update invoice.invoice_date_index set
	delivery_date = date_add(delivery_date, interval @intv_minus day)
	,create_date = date_add(create_date, interval @intv_minus day);
update invoice.invoice_date_index set
	delivery_date = date_add(delivery_date, interval @intv_plus day)
	,create_date = date_add(create_date, interval @intv_plus day);

-- 請求書ダウンロード履歴のクリアと初期設定
truncate table invoice.download_history_invoice;
insert into invoice.download_history_invoice
(invoice_id, member_id, admin_id, download_date, create_time)
select dsi.invoice_id, 1373, 0, date_add(dsi.create_date, interval 1 day), dsi.create_time 
from invoice.invoice_search_index dsi 
where dsi.create_date < @demo_date;

-- メールダウンロードキーの更新
update invoice.download_key set 
	create_time = (
		select isi.create_time 
		from invoice.invoice_search_index isi 
		where isi.invoice_id = 495672 )
where download_key = '4e126c8196324dd59858899077488953';