408 lines
20 KiB
Python
408 lines
20 KiB
Python
#!/usr/bin/env python
|
||
"""
|
||
old_rogdb から rogdb への全イベントデータ移行スクリプト（GPS情報含む）
|
||
FC岐阜の成功事例をベースに全てのイベントのteam/member/entry + GPS情報を移行
|
||
"""
|
||
|
||
import os
|
||
import sys
|
||
import django
|
||
|
||
if __name__ == '__main__':
|
||
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
|
||
django.setup()
|
||
|
||
from django.db import transaction, connection
|
||
from rog.models import NewEvent2, Team, Entry, Member, NewCategory, CustomUser
|
||
import psycopg2
|
||
|
||
print("📋 全イベントデータ移行スクリプト（GPS情報含む）を開始します")
|
||
|
||
# 各データベース接続設定
|
||
OLD_DB_CONFIG = {
|
||
'host': 'postgres-db',
|
||
'port': 5432,
|
||
'database': 'old_rogdb',
|
||
'user': 'postgres',
|
||
'password': 'password'
|
||
}
|
||
|
||
GIFUROGE_DB_CONFIG = {
|
||
'host': 'postgres-db',
|
||
'port': 5432,
|
||
'database': 'gifuroge',
|
||
'user': 'postgres',
|
||
'password': 'password'
|
||
}
|
||
|
||
try:
|
||
# データベース接続
|
||
old_conn = psycopg2.connect(**OLD_DB_CONFIG)
|
||
gifuroge_conn = psycopg2.connect(**GIFUROGE_DB_CONFIG)
|
||
|
||
with transaction.atomic():
|
||
# === STEP 1: ユーザー確認 ===
|
||
print("\n=== STEP 1: ユーザー確認 ===")
|
||
|
||
admin_user, created = CustomUser.objects.get_or_create(
|
||
username='admin',
|
||
defaults={
|
||
'email': 'admin@example.com',
|
||
'is_staff': True,
|
||
'is_superuser': True
|
||
}
|
||
)
|
||
print(f"管理ユーザー: {'作成' if created else '既存'}")
|
||
|
||
# === STEP 2: イベントとカテゴリー情報取得 ===
|
||
print("\n=== STEP 2: 既存イベント・カテゴリー確認 ===")
|
||
|
||
existing_events = list(NewEvent2.objects.values_list('id', 'name'))
|
||
print(f"既存イベント数: {len(existing_events)}件")
|
||
|
||
if not existing_events:
|
||
print("❌ イベントが存在しません。先にイベントを作成してください。")
|
||
sys.exit(1)
|
||
|
||
existing_categories = list(NewCategory.objects.values_list('id', 'name'))
|
||
print(f"既存カテゴリー数: {len(existing_categories)}件")
|
||
|
||
if not existing_categories:
|
||
print("❌ カテゴリーが存在しません。先にカテゴリーを作成してください。")
|
||
sys.exit(1)
|
||
|
||
# === STEP 3: チーム移行 ===
|
||
print("\n=== STEP 3: チーム移行 ===")
|
||
|
||
with old_conn.cursor() as cursor:
|
||
cursor.execute("""
|
||
SELECT id, name, owner_id, event_id, reg_date,
|
||
representative_name, representative_phone,
|
||
representative_email, is_deleted
|
||
FROM team
|
||
WHERE is_deleted = FALSE
|
||
ORDER BY id;
|
||
""")
|
||
old_teams = cursor.fetchall()
|
||
|
||
print(f"old_rogdbのチーム数: {len(old_teams)}件")
|
||
|
||
total_migrated_teams = 0
|
||
|
||
for team_data in old_teams:
|
||
old_team_id, name, owner_id, event_id, reg_date, rep_name, rep_phone, rep_email, is_deleted = team_data
|
||
|
||
# イベントが存在するかチェック
|
||
if not NewEvent2.objects.filter(id=event_id).exists():
|
||
continue
|
||
|
||
# チームが既に存在するかチェック
|
||
if Team.objects.filter(id=old_team_id).exists():
|
||
continue
|
||
|
||
try:
|
||
team = Team.objects.create(
|
||
id=old_team_id,
|
||
name=name,
|
||
owner_id=owner_id or admin_user.id,
|
||
event_id=event_id,
|
||
reg_date=reg_date,
|
||
representative_name=rep_name or name,
|
||
representative_phone=rep_phone or '',
|
||
representative_email=rep_email or '',
|
||
is_deleted=False
|
||
)
|
||
total_migrated_teams += 1
|
||
if total_migrated_teams <= 5:
|
||
print(f" チーム作成: {name} (ID: {old_team_id})")
|
||
|
||
except Exception as e:
|
||
print(f" ❌ チーム作成エラー: {name} - {e}")
|
||
|
||
print(f"✅ チーム移行完了: {total_migrated_teams}件作成")
|
||
|
||
# === STEP 4: メンバー移行 ===
|
||
print("\n=== STEP 4: メンバー移行 ===")
|
||
|
||
with old_conn.cursor() as cursor:
|
||
cursor.execute("""
|
||
SELECT id, team_id, name, kana, is_leader,
|
||
phone, email, birthday, gender, si_number, is_deleted
|
||
FROM member
|
||
WHERE is_deleted = FALSE
|
||
ORDER BY id;
|
||
""")
|
||
old_members = cursor.fetchall()
|
||
|
||
print(f"old_rogdbのメンバー数: {len(old_members)}件")
|
||
|
||
total_migrated_members = 0
|
||
|
||
for member_data in old_members:
|
||
old_member_id, team_id, name, kana, is_leader, phone, email, birthday, gender, si_number, is_deleted = member_data
|
||
|
||
# チームが存在するかチェック
|
||
if not Team.objects.filter(id=team_id).exists():
|
||
continue
|
||
|
||
# メンバーが既に存在するかチェック
|
||
if Member.objects.filter(id=old_member_id).exists():
|
||
continue
|
||
|
||
try:
|
||
member = Member.objects.create(
|
||
id=old_member_id,
|
||
team_id=team_id,
|
||
name=name,
|
||
kana=kana or '',
|
||
is_leader=is_leader or False,
|
||
phone=phone or '',
|
||
email=email or '',
|
||
birthday=birthday,
|
||
gender=gender or '',
|
||
si_number=si_number,
|
||
is_deleted=False
|
||
)
|
||
total_migrated_members += 1
|
||
if total_migrated_members <= 5:
|
||
print(f" メンバー作成: {name} (チーム{team_id})")
|
||
|
||
except Exception as e:
|
||
print(f" ❌ メンバー作成エラー: {name} - {e}")
|
||
|
||
print(f"✅ メンバー移行完了: {total_migrated_members}件作成")
|
||
|
||
# === STEP 5: エントリー移行 ===
|
||
print("\n=== STEP 5: エントリー移行 ===")
|
||
|
||
total_migrated_entries = 0
|
||
|
||
# イベント別にエントリーを移行
|
||
for event_id, event_name in existing_events:
|
||
print(f"\n 📊 {event_name} (ID: {event_id}) のエントリー移行中...")
|
||
|
||
# カテゴリーを取得（なければデフォルト使用）
|
||
cat_id = existing_categories[0][0] if existing_categories else 1
|
||
|
||
with old_conn.cursor() as cursor:
|
||
cursor.execute("""
|
||
SELECT t.id as team_id, t.name as team_name, t.owner_id,
|
||
s.zekken_number, s.label, s.is_deleted
|
||
FROM team t
|
||
LEFT JOIN start s ON t.id = s.team_id
|
||
WHERE t.event_id = %s AND t.is_deleted = FALSE
|
||
ORDER BY t.id;
|
||
""", [event_id])
|
||
|
||
entries_data = cursor.fetchall()
|
||
print(f" 対象エントリー数: {len(entries_data)}件")
|
||
|
||
event_migrated_entries = 0
|
||
|
||
for entry_data in entries_data:
|
||
team_id, team_name, owner_id, zekken, label, is_deleted = entry_data
|
||
|
||
# エントリーが既に存在するかチェック
|
||
if Entry.objects.filter(team_id=team_id, event_id=event_id).exists():
|
||
continue
|
||
|
||
try:
|
||
# チームとイベントの存在確認
|
||
team_obj = Team.objects.get(id=team_id)
|
||
event_obj = NewEvent2.objects.get(id=event_id)
|
||
|
||
# Entryオブジェクト作成
|
||
entry = Entry.objects.create(
|
||
date=event_obj.start_datetime,
|
||
category_id=cat_id,
|
||
event_id=event_id,
|
||
owner_id=owner_id or admin_user.id,
|
||
team_id=team_id,
|
||
is_active=True,
|
||
zekken_number=int(zekken) if zekken else 0,
|
||
hasGoaled=False,
|
||
hasParticipated=False,
|
||
zekken_label=label or f"{event_name}-{zekken}",
|
||
is_trial=False,
|
||
staff_privileges=False,
|
||
can_access_private_events=False,
|
||
team_validation_status='approved'
|
||
)
|
||
|
||
event_migrated_entries += 1
|
||
total_migrated_entries += 1
|
||
if event_migrated_entries <= 3:
|
||
print(f" エントリー作成: {team_name} - ゼッケン{zekken}")
|
||
|
||
except Team.DoesNotExist:
|
||
print(f" ❌ チーム{team_id}が見つかりません: {team_name}")
|
||
except NewEvent2.DoesNotExist:
|
||
print(f" ❌ イベント{event_id}が見つかりません")
|
||
except Exception as e:
|
||
print(f" ❌ エントリー作成エラー: {team_name} - {e}")
|
||
|
||
print(f" ✅ {event_name}: {event_migrated_entries}件のエントリーを移行")
|
||
|
||
print(f"\n✅ 全エントリー移行完了: {total_migrated_entries}件作成")
|
||
|
||
# === STEP 6: GPS情報移行 ===
|
||
print("\n=== STEP 6: GPS情報（通過データ）移行 ===")
|
||
|
||
with gifuroge_conn.cursor() as gifuroge_cursor:
|
||
# GPS情報データ数確認
|
||
gifuroge_cursor.execute("SELECT COUNT(*) FROM gps_information;")
|
||
gps_total_count = gifuroge_cursor.fetchone()[0]
|
||
print(f"GPS情報総数: {gps_total_count}件")
|
||
|
||
if gps_total_count > 0:
|
||
# ロガインDBからteam_idとzekken_numberの対応関係を取得
|
||
print("\n 📊 チーム-ゼッケン対応表作成中...")
|
||
team_zekken_map = {}
|
||
|
||
with old_conn.cursor() as old_cursor:
|
||
old_cursor.execute("""
|
||
SELECT t.id as team_id, s.zekken_number, t.event_id
|
||
FROM team t
|
||
LEFT JOIN start s ON t.id = s.team_id
|
||
WHERE t.is_deleted = FALSE AND s.zekken_number IS NOT NULL;
|
||
""")
|
||
team_zekken_data = old_cursor.fetchall()
|
||
|
||
for team_id, zekken_number, event_id in team_zekken_data:
|
||
if zekken_number:
|
||
team_zekken_map[str(zekken_number)] = {
|
||
'team_id': team_id,
|
||
'event_id': event_id
|
||
}
|
||
|
||
print(f" チーム-ゼッケン対応: {len(team_zekken_map)}件")
|
||
|
||
# GPS情報をバッチで移行
|
||
print("\n 🌍 GPS情報移行中...")
|
||
|
||
# 既存のGPS情報をクリア（必要に応じて）
|
||
with connection.cursor() as django_cursor:
|
||
django_cursor.execute("SELECT COUNT(*) FROM gps_checkins;")
|
||
existing_gps = django_cursor.fetchone()[0]
|
||
print(f" 既存GPS記録: {existing_gps}件")
|
||
|
||
# GPS情報を取得・移行
|
||
gifuroge_cursor.execute("""
|
||
SELECT zekken_number, event_code, cp_number, lattitude, longitude,
|
||
image_address, image_receipt, image_qr, validate_location,
|
||
goal_time, late_point, create_at, create_user, update_at,
|
||
update_user, buy_flag, colabo_company_memo, points
|
||
FROM gps_information
|
||
ORDER BY create_at;
|
||
""")
|
||
|
||
gps_records = gifuroge_cursor.fetchall()
|
||
print(f" 移行対象GPS記録: {len(gps_records)}件")
|
||
|
||
migrated_gps_count = 0
|
||
batch_size = 1000
|
||
|
||
with connection.cursor() as django_cursor:
|
||
for i in range(0, len(gps_records), batch_size):
|
||
batch = gps_records[i:i+batch_size]
|
||
print(f" バッチ {i//batch_size + 1}: {len(batch)}件処理中...")
|
||
|
||
for gps_record in batch:
|
||
(zekken_number, event_code, cp_number, lattitude, longitude,
|
||
image_address, image_receipt, image_qr, validate_location,
|
||
goal_time, late_point, create_at, create_user, update_at,
|
||
update_user, buy_flag, colabo_company_memo, points) = gps_record
|
||
|
||
# zekken_numberから対応するteam_idを取得
|
||
team_info = team_zekken_map.get(str(zekken_number))
|
||
team_id = team_info['team_id'] if team_info else None
|
||
event_id = team_info['event_id'] if team_info else None
|
||
|
||
try:
|
||
# gps_checkinsテーブルに実際の構造に合わせて挿入
|
||
django_cursor.execute("""
|
||
INSERT INTO gps_checkins (
|
||
path_order, zekken_number, event_code, cp_number,
|
||
lattitude, longitude, image_address, image_receipt,
|
||
image_qr, validate_location, goal_time, late_point,
|
||
create_at, create_user, update_at, update_user,
|
||
buy_flag, colabo_company_memo, points, event_id,
|
||
team_id, validation_status
|
||
) VALUES (
|
||
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
||
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
|
||
%s, %s
|
||
);
|
||
""", [
|
||
0, # path_order（デフォルト値）
|
||
str(zekken_number), # zekken_number
|
||
event_code, # event_code
|
||
cp_number, # cp_number
|
||
lattitude, # lattitude
|
||
longitude, # longitude
|
||
image_address, # image_address
|
||
image_receipt, # image_receipt
|
||
bool(image_qr) if image_qr is not None else False, # image_qr
|
||
bool(validate_location) if validate_location is not None else False, # validate_location
|
||
goal_time, # goal_time
|
||
late_point, # late_point
|
||
create_at, # create_at
|
||
create_user, # create_user
|
||
update_at, # update_at
|
||
update_user, # update_user
|
||
bool(buy_flag) if buy_flag is not None else False, # buy_flag
|
||
colabo_company_memo or '', # colabo_company_memo
|
||
points, # points
|
||
event_id, # event_id
|
||
team_id, # team_id
|
||
'pending' # validation_status（デフォルト値）
|
||
])
|
||
migrated_gps_count += 1
|
||
|
||
except Exception as e:
|
||
if migrated_gps_count < 5: # 最初の5件のエラーのみ表示
|
||
print(f" ❌ GPS記録移行エラー: ゼッケン{zekken_number} - {e}")
|
||
|
||
# バッチごとにコミット
|
||
connection.commit()
|
||
|
||
print(f" ✅ GPS情報移行完了: {migrated_gps_count}件作成")
|
||
else:
|
||
print(" 📍 GPS情報が存在しません")
|
||
|
||
old_conn.close()
|
||
gifuroge_conn.close()
|
||
|
||
# === 最終確認 ===
|
||
print("\n=== 移行結果確認 ===")
|
||
|
||
total_teams = Team.objects.count()
|
||
total_members = Member.objects.count()
|
||
total_entries = Entry.objects.count()
|
||
|
||
# GPS情報確認
|
||
with connection.cursor() as cursor:
|
||
cursor.execute("SELECT COUNT(*) FROM gps_checkins;")
|
||
total_gps = cursor.fetchone()[0]
|
||
|
||
print(f"総チーム数: {total_teams}件")
|
||
print(f"総メンバー数: {total_members}件")
|
||
print(f"総エントリー数: {total_entries}件")
|
||
print(f"総GPS記録数: {total_gps}件")
|
||
|
||
# イベント別エントリー統計
|
||
print("\n=== イベント別エントリー統計 ===")
|
||
for event_id, event_name in existing_events[:10]:
|
||
entry_count = Entry.objects.filter(event_id=event_id).count()
|
||
if entry_count > 0:
|
||
print(f" {event_name}: {entry_count}件")
|
||
|
||
print("\n🎉 全イベントデータ移行（GPS情報含む）が完了しました！")
|
||
print("🎯 通過審査管理画面で全てのイベントのゼッケン番号が表示され、")
|
||
print(" GPS情報による通過データも利用可能になります。")
|
||
|
||
except Exception as e:
|
||
print(f"❌ エラーが発生しました: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|