q

class SQLLoaderUtility {
    [string]$Setsuzoku
    [string]$BaseFolder
    [string]$ErrorFolder

    SQLLoaderUtility([string]$setsuzoku, [string]$baseFolder, [string]$errorFolder) {
        $this.Setsuzoku = $setsuzoku
        $this.BaseFolder = $baseFolder
        $this.ErrorFolder = $errorFolder
    }

    [string]ExecSQLPLUS([string]$sqlstate) {
        $command = @"
echo '$sqlstate' | sqlplus -S -L $($this.Setsuzoku)
"@
        Write-Host "Executing SQLPLUS Command: $command"
        return (Invoke-Expression -Command $command)
    }

    [void]BackupTable([string]$tableName) {
        $folderPath = Join-Path -Path $this.BaseFolder -ChildPath $tableName
        if (-not (Test-Path -Path $folderPath)) {
            New-Item -ItemType Directory -Path $folderPath | Out-Null
        }
        $sql = "SPOOL ${folderPath}\backup_${tableName}.log
                CREATE TABLE ${tableName}_backup AS SELECT * FROM $tableName;
                SPOOL OFF"
        $this.ExecSQLPLUS($sql)
    }

    [void]LoadData([string]$tableName, [string]$controlFile) {
        $folderPath = Join-Path -Path $this.BaseFolder -ChildPath $tableName
        $logFile = Join-Path -Path $folderPath -ChildPath "import_$tableName.log"
        $badFile = Join-Path -Path $folderPath -ChildPath "import_$tableName.bad"
        $discardFile = Join-Path -Path $folderPath -ChildPath "import_$tableName.dsc"

        $command = "sqlldr $($this.Setsuzoku) control=C:\ITK_Powershell\SQLLoader\ctl\I044\$controlFile log=$logFile bad=$badFile discard=$discardFile"
        Write-Host "Executing SQLLDR Command: $command"
        Invoke-Expression -Command $command
    }

    [bool]CheckBadFile([string]$tableName) {
        $folderPath = Join-Path -Path $this.BaseFolder -ChildPath $tableName
        $badFile = Join-Path -Path $folderPath -ChildPath "import_$tableName.bad"
        if (Test-Path $badFile) {
            $badFileContent = Get-Content $badFile
            if ($badFileContent.Length -gt 0) {
                return $true
            }
        }
        return $false
    }

    [void]RestoreTable([string]$tableName) {
        $sql = "DELETE FROM $tableName;
                INSERT INTO $tableName SELECT * FROM ${tableName}_backup;
                DROP TABLE ${tableName}_backup;"
        $this.ExecSQLPLUS($sql)
    }

    [void]DropBackupTable([string]$tableName) {
        $sql = "DROP TABLE ${tableName}_backup;"
        $this.ExecSQLPLUS($sql)
    }

    [void]ArchiveLogs([hashtable]$tables) {
        if (-not (Test-Path -Path $this.ErrorFolder)) {
            New-Item -ItemType Directory -Path $this.ErrorFolder | Out-Null
        }
        foreach ($table in $tables.Keys) {
            $folderPath = Join-Path -Path $this.BaseFolder -ChildPath $table
            $zipFile = Join-Path -Path $this.ErrorFolder -ChildPath "${table}_logs.zip"
            Compress-Archive -Path "$folderPath\*" -DestinationPath $zipFile -Force

            # Delete the log files after archiving
            Get-ChildItem -Path $folderPath -File | Remove-Item -Force
        }
    }

    [void]ProcessTables([hashtable]$tables) {
        $errors = $false

        foreach ($table in $tables.Keys) {
            try {
                $this.BackupTable($table)
                $this.LoadData($table, $tables[$table])
                if ($this.CheckBadFile($table)) {
                    $errors = $true
                    Write-Host "Errors detected in table $table. Check the bad file for details."
                    break
                }
            } catch {
                $errors = $true
                Write-Host "An error occurred while processing table $table : $_"
                break
            }
        }

        if ($errors) {
            Write-Host "Errors detected. Archiving logs and restoring previous data..."
            $this.ArchiveLogs($tables)
            foreach ($table in $tables.Keys) {
                try {
                    $this.RestoreTable($table)
                } catch {
                    Write-Host "An error occurred while restoring table $table : $_"
                }
            }
        } else {
            Write-Host "No errors detected. Proceeding with new data."
            foreach ($table in $tables.Keys) {
                try {
                    $this.DropBackupTable($table)
                } catch {
                    Write-Host "An error occurred while dropping backup table $table : $_"
                }
            }
        }
    }
}
# Usage example
$tables = @{
    "EMPLOYEE_PROJECTS" = "control_file1.ctl"
#    "EMPLOYEES"         = "control_file2.ctl"
#    "PROJECTS"          = "control_file3.ctl"
#    "SALARIES"          = "control_file4.ctl"
}
$sqlLoaderUtility = [SQLLoaderUtility]::new($setsuzoku, $baseFolder, $errorFolder)
$sqlLoaderUtility.ProcessTables($tables)

OPTIONS (
  -- SKIP行目はスキップ
  SKIP=1,
  -- ERRORS件までエラーは許容
  ERRORS=1,
  -- ROWS件ごとにコミット
  ROWS=10
)
LOAD DATA
  -- 読み込むファイル
  INFILE '.\EMPLOYEE_PROJECTS.csv'
  -- 不良ファイル。ロードしようとしてはじかれたレコード
  -- BADFILE '.\bad\EMPLOYEE_PROJECTS.bad'
  -- 廃棄ファイル。ロード対象として不正とみなされたレコード
  -- DISCARDFILE '.\dsc\EMPLOYEE_PROJECTS.dsc'
  -- テーブルはあらかじめ消す
  TRUNCATE INTO TABLE EMPLOYEE_PROJECTS
  -- 256バイト以上の場合はエラーが発生するので桁数を指定
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  (
      EMPLOYEE_ID,
      PROJECT_ID,
      ROLE
  )