Top_FileGeneralPurpose.ps1
# =============================================================================
$global:ScriptDir = pwd
$global:ParaPath = ".\Com_Para.ini"
$global:Para = @{}
(Get-Content $global:ParaPath -Raw -Encoding UTF8).Replace('\','\\') | `
ForEach-Object{$global:Para += ConvertFrom-StringData $_}
$global:TargetHH = [int]$global:Para.TargetHour
$global:TargetMM = [int]$global:Para.TargetMinute
$global:ProcessTime = "[ 処理時刻 $global:TargetHH`:$global:TargetMM ]"
$global:Count = ""
# =============================================================================
class Read_Csv_Sql {
[string]$CsvDirectory
[string]$Query
[string]$ConnectionString
# -------------------------------------------------------------------------
Read_Csv_Sql([string]$csvDirectory, [string]$query) {
$this.CsvDirectory = $csvDirectory
$this.Query = $query
$this.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;`
Data Source=$($this.CsvDirectory);`
Extended Properties='text;HDR=YES;FMT=Delimited(,)';"
}
# -------------------------------------------------------------------------
[System.Data.DataTable] ExecuteQuery() {
$conn = New-Object System.Data.OleDb.OleDbConnection(`
$this.ConnectionString)
try {
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $this.Query
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$table = New-Object System.Data.DataTable
$adapter.Fill($table) | Out-Null
}
catch {
return $null
}
finally {
$conn.Close()
}
return $table
}
# -------------------------------------------------------------------------
[System.Data.DataTable] GetAllData() {
return $this.ExecuteQuery()
}
}
# =============================================================================
class Dictionary {
[hashtable]$Items
# -------------------------------------------------------------------------
Dictionary() {
$this.Items = @{}
}
# -------------------------------------------------------------------------
[void] AddItem([string]$key, [string]$value) {
if ($this.Items.ContainsKey($key)) {
} else {
$this.Items.Add($key, $value)
}
}
# -------------------------------------------------------------------------
[string] GetItem([string]$key) {
if ($this.Items.ContainsKey($key)) {
return $this.Items[$key]
} else {
return 0
}
}
# -------------------------------------------------------------------------
[array] GetAllItems() {
$result = @()
foreach ($key in $this.Items.Keys) {
try {
$value = $this.Items[$key]
$result += "${key} : ${value}"
} catch {
$result += "Error processing item : $key"
$result += $_.Exception.Message
}
}
return $result
}
}
# =============================================================================
class Read_Excel_Sql {
[string]$ExcelFilePath
[string]$Query
[string]$ConnectionString
# -------------------------------------------------------------------------
Read_Excel_Sql([string]$excelFilePath, [string]$query) {
$this.ExcelFilePath = $excelFilePath
$this.Query = $query
$this.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " +
"Data Source=`"$($this.ExcelFilePath)`"; " +
"Extended Properties=`"Excel 12.0 Xml;HDR=YES;`";"
}
# -------------------------------------------------------------------------
[System.Data.DataTable] ExecuteQuery() {
$conn = New-Object System.Data.OleDb.OleDbConnection(`
$this.ConnectionString)
try {
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $this.Query
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$table = New-Object System.Data.DataTable
$adapter.Fill($table) | Out-Null
} catch {
return $null
}
finally {
$conn.Close()
}
return $table
}
# -------------------------------------------------------------------------
[System.Data.DataTable] GetAllData() {
return $this.ExecuteQuery()
}
}
# =============================================================================
class OutPut_Txt {
[string] $FilePath
# -------------------------------------------------------------------------
OutPut_Txt([string] $filePath) {
$this.FilePath = $filePath
}
# -------------------------------------------------------------------------
[void] WriteMessage([string] $message) {
try {
$message | Out-File -FilePath $this.FilePath -Append -Encoding UTF8
} catch {
Write-Error "ファイルの書き込み中にエラーが発生しました: $_"
}
}
}
# =============================================================================
# 日付取得
function Fun_GetDay {
param (
[string]$para
)
$now = Get-Date
return $now.ToString($para)
}
# =============================================================================
# ファイル存在確認
function FileExists {
param (
[string]$filePath
)
if (Test-Path -Path $filePath) {
return $true
} else {
return $false
}
}
# =============================================================================
# YES/NOメッセージボックス
function Show-YesNoMessageBox {
param (
[string]$message = "閉じる場合は ""はい"" を押下",
[string]$title = "確認"
)
Add-Type -AssemblyName System.Windows.Forms
$result = [System.Windows.Forms.MessageBox]::Show(
$message, # メッセージ
$title, # タイトル
[System.Windows.Forms.MessageBoxButtons]::YesNo, # ボタンの種類
[System.Windows.Forms.MessageBoxIcon]::Question # アイコンの種類
)
return $result
}
# =============================================================================
# OKメッセージボックス
function Show-OkMessageBox {
param (
[string]$message,
[string]$title = "確認"
)
Add-Type -AssemblyName System.Windows.Forms
$result = [System.Windows.Forms.MessageBox]::Show(
$message, # メッセージ
$title, # タイトル
[System.Windows.Forms.MessageBoxButtons]::OK, # ボタンの種類
[System.Windows.Forms.MessageBoxIcon]::Information # アイコンの種類
)
return $result
}
# =============================================================================
function Main {
try {
# ---------------------------------------------------------------------
$process = "01.01|事前準備 ログファイル準備"
New-Item -Path "$global:ScriptDir\Log" -ItemType Directory -Force | Out-Null
$yearMonthDay = Fun_GetDay "yyyyMMdd"
$compfilePath = "$global:ScriptDir\Log\Comp_$yearMonthDay.txt"
$compLog = [OutPut_Txt]::new($compfilePath)
$errfilePath = "$global:ScriptDir\Log\Err_$yearMonthDay.txt"
$errLog = [OutPut_Txt]::new($errfilePath)
# ---------------------------------------------------------------------
$process = "02.01|マスタファイル読込み"
$excelQuery = [Read_Excel_Sql]::new($global:Para.Fi_Product, `
$global:Para.Qu_Product)
$excelData = $excelQuery.GetAllData()
if ($null -eq $excelData) {
$hhmmss = Fun_GetDay "HH:mm:ss"
$errLog.WriteMessage($hhmmss + "|" + $process + "|データの読込み失敗")
return
}
# ---------------------------------------------------------------------
$process = "03.01|マスタを連想配列格納"
$dictionary = [Dictionary]::new()
foreach ($row in $excelData.Rows) {
$dictionary.AddItem($row[$global:Para.Key], `
$row[$global:Para.Value])
}
# ---------------------------------------------------------------------
$process = "04.01|CSVファイル読み込み"
$csvQuery = [Read_Csv_Sql]::new($global:Para.Fo_InFile, `
$global:Para.Qu_CsvFile)
$csvData = $csvQuery.GetAllData()
if ($null -eq $csvData) {
$hhmmss = Fun_GetDay "HH:mm:ss"
$errLog.WriteMessage($hhmmss + "|" + $process + "|データの読込み失敗")
return
}
# ---------------------------------------------------------------------
$process = "05.01|CSVに和名追記"
foreach ($row in $csvData.Rows) {
$repValue = $global:Para.Value
$repKey = $global:Para.Key
$key = $row[$repKey]
if (0 -ne $dictionary.GetItem($key)) {
$row[$repValue] = $dictionary.GetItem($key)
}
}
# ---------------------------------------------------------------------
$process = "06.01|CSV出力"
$hhmmss = Fun_GetDay "HHmmss"
$outputPath = $global:Para.Fo_OutFile + "\注文_$yearMonthDay-$hhmmss.csv"
$csvData | Export-Csv -Path $outputPath -NoTypeInformation -Encoding UTF8
# ---------------------------------------------------------------------
$process = "98.01|正常終了"
$hhmmss = Fun_GetDay "HH:mm:ss"
$compLog.WriteMessage("$hhmmss|$process|Congratulation!!")
# ---------------------------------------------------------------------
}
catch {
# -------------------------------------------------------------------------
$process = "99.01|異常終了 >>> " + $process
$hhmmss = Fun_GetDay "HH:mm:ss"
$errLog.WriteMessage("$hhmmss|$process|$_.Exception.Message")
# -------------------------------------------------------------------------
}
}
# +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Windows From
# +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# 必要なアセンブリをロード
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
# =============================================================================
# カウンター
function SetCounter {
if ($global:Count.Length -eq 0) {
$global:Count = " >"
$label.Text = "処理時刻待ち $global:Count"
} elseif ($global:Count.Length -eq 2) {
$global:Count = " > >"
$label.Text = "処理時刻待ち $global:Count"
} elseif ($global:Count.Length -eq 4) {
$global:Count = " > > >"
$label.Text = "処理時刻待ち $global:Count"
} elseif ($global:Count.Length -eq 6) {
$global:Count = " > > > >"
$label.Text = "処理時刻待ち $global:Count"
} elseif ($global:Count.Length -eq 8) {
$global:Count = " > > > > >"
$label.Text = "処理時刻待ち $global:Count"
} else {
$global:Count = ""
$label.Text = "処理時刻待ち $global:Count"
}
}
# -----------------------------------------------------------------------------
# タイマーの作成
$timer = New-Object System.Windows.Forms.Timer
$timer.Interval = 1000 # 1秒ごとにチェック
# -----------------------------------------------------------------------------
# タイマーのTickイベントハンドラ
$timer.Add_Tick({
$yearMonthDay = Fun_GetDay "yyyyMMdd"
$comp_filePath = "$global:ScriptDir\Log\Comp_$yearMonthDay.txt"
$comp_result = FileExists -filePath $comp_filePath
$err_filePath = "$global:ScriptDir\Log\Err_$yearMonthDay.txt"
$err_result = FileExists -filePath $err_filePath
$currentTime = Get-Date
if ($currentTime.Hour -eq $global:TargetHH -and `
$currentTime.Minute -eq $global:TargetMM -and `
$false -eq $comp_result -and `
$false -eq $err_result) {
$label.Text = "処理中..."
Main
# タスクが完了したら
$label.Text = "処理時刻待ち $global:Count"
}
SetCounter
})
# -----------------------------------------------------------------------------
# サブフォーム
$subform = New-Object System.Windows.Forms.Form
$subform.Text = "設定"
$subform.Size = New-Object System.Drawing.Size(450, 510)
$subform.StartPosition = "CenterScreen"
$subform.Font = New-Object System.Drawing.Font("メイリオ", 8, `
[System.Drawing.FontStyle]::Regular)
# -----------------------------------------------------------------------------
# ラベル(製品マスタ)
$label_Product = New-Object System.Windows.Forms.Label
$label_Product.Text = "製品マスタ ファイルパス"
$label_Product.AutoSize = $true
$label_Product.Location = New-Object System.Drawing.Point(10, 10)
$subform.Controls.Add($label_Product)
# -----------------------------------------------------------------------------
# テキストボックス(製品マスタ)
$textbox_Product = New-Object System.Windows.Forms.TextBox
$textbox_Product.Location = New-Object System.Drawing.Point(10, 32)
$textbox_Product.Size = New-Object System.Drawing.Size(410, 20)
$textbox_Product.Text = $global:Para.Fi_Product
$subform.Controls.Add($textbox_Product)
# -----------------------------------------------------------------------------
# ラベル(インプットフォルダパス)
$label_InFilePath = New-Object System.Windows.Forms.Label
$label_InFilePath.Text = "インプットフォルダパス"
$label_InFilePath.AutoSize = $true
$label_InFilePath.Location = New-Object System.Drawing.Point(10, 78)
$subform.Controls.Add($label_InFilePath)
# -----------------------------------------------------------------------------
# テキストボックス(インプットフォルダパス)
$textbox_InFilePath = New-Object System.Windows.Forms.TextBox
$textbox_InFilePath.Location = New-Object System.Drawing.Point(10, 100)
$textbox_InFilePath.Size = New-Object System.Drawing.Size(410, 20)
$textbox_InFilePath.Text = $global:Para.Fo_InFile
$subform.Controls.Add($textbox_InFilePath)
# -----------------------------------------------------------------------------
# ラベル(アウトプットフォルダパス)
$label_OutFilePath = New-Object System.Windows.Forms.Label
$label_OutFilePath.Text = "アウトプットフォルダパス"
$label_OutFilePath.AutoSize = $true
$label_OutFilePath.Location = New-Object System.Drawing.Point(10, 146)
$subform.Controls.Add($label_OutFilePath)
# -----------------------------------------------------------------------------
# テキストボックス(アウトプットフォルダパス)
$textbox_OutFilePath = New-Object System.Windows.Forms.TextBox
$textbox_OutFilePath.Location = New-Object System.Drawing.Point(10, 168)
$textbox_OutFilePath.Size = New-Object System.Drawing.Size(410, 20)
$textbox_OutFilePath.Text = $global:Para.Fo_OutFile
$subform.Controls.Add($textbox_OutFilePath)
# -----------------------------------------------------------------------------
# ラベル(開始時間)
$label_Start = New-Object System.Windows.Forms.Label
$label_Start.Text = "開始時間"
$label_Start.AutoSize = $true
$label_Start.Location = New-Object System.Drawing.Point(10, 214)
$subform.Controls.Add($label_Start)
# -----------------------------------------------------------------------------
# テキストボックス(開始時間)
$textbox_Start = New-Object System.Windows.Forms.TextBox
$textbox_Start.Location = New-Object System.Drawing.Point(10, 236)
$textbox_Start.Size = New-Object System.Drawing.Size(50, 20)
$textbox_Start.TextAlign = "Center"
$textbox_Start.Text = $global:Para.TargetHour
$subform.Controls.Add($textbox_Start)
# -----------------------------------------------------------------------------
# ラベル(終了時間)
$label_End = New-Object System.Windows.Forms.Label
$label_End.Text = "終了時間"
$label_End.AutoSize = $true
$label_End.Location = New-Object System.Drawing.Point(80, 214)
$subform.Controls.Add($label_End)
# -----------------------------------------------------------------------------
# テキストボックス(終了時間)
$textbox_End = New-Object System.Windows.Forms.TextBox
$textbox_End.Location = New-Object System.Drawing.Point(80, 236)
$textbox_End.Size = New-Object System.Drawing.Size(50, 20)
$textbox_End.TextAlign = "Center"
$textbox_End.Text = $global:Para.TargetMinute
$subform.Controls.Add($textbox_End)
# -----------------------------------------------------------------------------
# ラベル(変換 Key)
$label_Key = New-Object System.Windows.Forms.Label
$label_Key.Text = "変換 Key"
$label_Key.AutoSize = $true
$label_Key.Location = New-Object System.Drawing.Point(150, 214)
$subform.Controls.Add($label_Key)
# -----------------------------------------------------------------------------
# テキストボックス(変換 Key)
$textbox_Key = New-Object System.Windows.Forms.TextBox
$textbox_Key.Location = New-Object System.Drawing.Point(150, 236)
$textbox_Key.Size = New-Object System.Drawing.Size(100, 20)
$textbox_Key.TextAlign = "Center"
$textbox_Key.Text = $global:Para.Key
$subform.Controls.Add($textbox_Key)
# -----------------------------------------------------------------------------
# ラベル(変換 Value)
$label_Value = New-Object System.Windows.Forms.Label
$label_Value.Text = "変換 Value"
$label_Value.AutoSize = $true
$label_Value.Location = New-Object System.Drawing.Point(270, 214)
$subform.Controls.Add($label_Value)
# -----------------------------------------------------------------------------
# テキストボックス(変換 Value)
$textbox_Value = New-Object System.Windows.Forms.TextBox
$textbox_Value.Location = New-Object System.Drawing.Point(270, 236)
$textbox_Value.Size = New-Object System.Drawing.Size(100, 20)
$textbox_Value.TextAlign = "Center"
$textbox_Value.Text = $global:Para.Value
$subform.Controls.Add($textbox_Value)
# -----------------------------------------------------------------------------
# ラベル(インプットファイル用 SQL)
$label_InFileSQL = New-Object System.Windows.Forms.Label
$label_InFileSQL.Text = "インプットファイル用 SQL"
$label_InFileSQL.AutoSize = $true
$label_InFileSQL.Location = New-Object System.Drawing.Point(10, 282)
$subform.Controls.Add($label_InFileSQL)
# -----------------------------------------------------------------------------
# テキストボックス(インプットファイル用 SQL)
$textbox_InFileSQL = New-Object System.Windows.Forms.TextBox
$textbox_InFileSQL.Location = New-Object System.Drawing.Point(10, 304)
$textbox_InFileSQL.Size = New-Object System.Drawing.Size(410, 20)
$textbox_InFileSQL.Text = $global:Para.Qu_CsvFile
$subform.Controls.Add($textbox_InFileSQL)
# -----------------------------------------------------------------------------
# ラベル(マスタ用 SQL)
$label_MasterSQL = New-Object System.Windows.Forms.Label
$label_MasterSQL.Text = "マスタ用 SQL"
$label_MasterSQL.AutoSize = $true
$label_MasterSQL.Location = New-Object System.Drawing.Point(10, 350)
$subform.Controls.Add($label_MasterSQL)
# -----------------------------------------------------------------------------
# テキストボックス(マスタ用 SQL)
$textbox_MasterSQL = New-Object System.Windows.Forms.TextBox
$textbox_MasterSQL.Location = New-Object System.Drawing.Point(10, 372)
$textbox_MasterSQL.Size = New-Object System.Drawing.Size(410, 20)
$textbox_MasterSQL.Text = $global:Para.Qu_Product
$subform.Controls.Add($textbox_MasterSQL)
# -----------------------------------------------------------------------------
# ボタン1
$subButton1 = New-Object System.Windows.Forms.Button
$subButton1.Text = "保存"
$subButton1.Size = New-Object System.Drawing.Size(120, 26)
$subButton1.Location = New-Object System.Drawing.Point(300, 420)
# -----------------------------------------------------------------------------
# ボタン1 (保存)
$subButton1.Add_Click({
$global:Para.Fi_Product = $textbox_Product.Text
$global:Para.Fo_InFile = $textbox_InFilePath.Text
$global:Para.Fo_OutFile = $textbox_OutFilePath.Text
$global:Para.TargetHour = $textbox_Start.Text
$global:Para.TargetMinute = $textbox_End.Text
$content = $global:Para.GetEnumerator() | ForEach-Object {
"$($_.Key) = $($_.Value)"
}
$content | Out-File -FilePath $global:ParaPath -Encoding UTF8
$message = "保存しました。"
Show-OkMessageBox -message $message
$subform.Close()
})
$subform.Controls.Add($subButton1)
# -----------------------------------------------------------------------------
# フォームの作成
$form = New-Object System.Windows.Forms.Form
$form.Text = "FileGeneralPurpose"
$form.Size = New-Object System.Drawing.Size(320, 140)
$form.StartPosition = "CenterScreen"
$form.Font = New-Object System.Drawing.Font("メイリオ", 9, `
[System.Drawing.FontStyle]::Regular)
# -----------------------------------------------------------------------------
# ラベルの作成(ステータス)
$label = New-Object System.Windows.Forms.Label
$label.Text = "停止中"
$label.AutoSize = $true
$label.Location = New-Object System.Drawing.Point(20, 30)
$form.Controls.Add($label)
# -----------------------------------------------------------------------------
# ラベルの作成(処理時刻)
$processLabel = New-Object System.Windows.Forms.Label
$processLabel.Text = $global:ProcessTime
$processLabel.AutoSize = $true
$processLabel.Location = New-Object System.Drawing.Point(180, 30)
$form.Controls.Add($processLabel)
# -----------------------------------------------------------------------------
# ボタン1
$button1 = New-Object System.Windows.Forms.Button
$button1.Text = "開始"
$button1.Size = New-Object System.Drawing.Size(70, 26)
$button1.Location = New-Object System.Drawing.Point(20, 60)
# -----------------------------------------------------------------------------
# ボタン1 (実行)
$button1.Add_Click({
$global:Para.Clear()
(Get-Content $paraPath -Raw -Encoding UTF8).Replace('\','\\') | `
ForEach-Object{$global:Para += ConvertFrom-StringData $_}
$global:TargetHH = [int]$global:Para.TargetHour
$global:TargetMM = [int]$global:Para.TargetMinute
$global:ProcessTime = "[ 処理時刻 $global:TargetHH`:$global:TargetMM ]"
$processLabel.Text = $global:ProcessTime
$label.Text = "処理時刻待ち $global:Count"
$timer.Start()
})
# -----------------------------------------------------------------------------
# ボタン2
$button2 = New-Object System.Windows.Forms.Button
$button2.Text = "停止"
$button2.Size = New-Object System.Drawing.Size(70, 26)
$button2.Location = New-Object System.Drawing.Point(95, 60)
# -----------------------------------------------------------------------------
# ボタン2 (停止)
$button2.Add_Click({
$label.Text = "停止中"
$timer.Stop()
})
# -----------------------------------------------------------------------------
# ボタン3
$button3 = New-Object System.Windows.Forms.Button
$button3.Text = "設定"
$button3.Size = New-Object System.Drawing.Size(50, 26)
$button3.Location = New-Object System.Drawing.Point(170, 60)
# -----------------------------------------------------------------------------
# ボタン3 (設定)
$button3.Add_Click({
# Start-Process notepad.exe $global:ParaPath
$Subform.ShowDialog()
})
# -----------------------------------------------------------------------------
# ボタン4
$button4 = New-Object System.Windows.Forms.Button
$button4.Text = "閉じる"
$button4.Size = New-Object System.Drawing.Size(60, 26)
$button4.Location = New-Object System.Drawing.Point(225, 60)
# -----------------------------------------------------------------------------
# ボタン4 (閉じる)
$button4.Add_Click({
$form.Close()
})
# -----------------------------------------------------------------------------
# アイコン
$iconFilePath = "$global:ScriptDir\FileGeneralPurpose.ico"
if (Test-Path $iconFilePath) {
$icon = New-Object System.Drawing.Icon $iconFilePath
$form.Icon = $icon
$subform.Icon = $icon
} else {
Write-Host "指定されたアイコンファイルが見つかりませんでした。"
}
# -----------------------------------------------------------------------------
# フォームのバツボタン無効化
$form.Add_FormClosing({
param([System.Object]$sender, [System.ComponentModel.CancelEventArgs]$e)
$e.Cancel = $true
$response = Show-YesNoMessageBox
if ($response -eq [System.Windows.Forms.DialogResult]::Yes) {
$mutexObject.ReleaseMutex() # ミューテックスを解放します。
$mutexObject.Close() # ミューテックスのリソースを解放します。
$e.Cancel = $false
}
})
# -------------------------------------------------y----------------------------
# フォームを表示
$mutexObject = New-Object System.Threading.Mutex($false, "Global¥MUTEX_TEST")
# シグナルを受信します。戻値がFalseの場合、処理を終了します。
# ※ Falseの場合、別のスレッドでミューテックスが作成されています。
if (-not $mutexObject.WaitOne(0, $false)) {
Write-Warning "既に処理が実行されています。"
exit
}
$form.Controls.Add($button1)
$form.Controls.Add($button2)
$form.Controls.Add($button3)
$form.Controls.Add($button4)
$form.Add_Shown({$form.Activate()})
[System.Windows.Forms.Application]::Run($form)
# -----------------------------------------------------------------------------
# 前回の異常終了検出処理
trap [System.Threading.AbandonedMutexException] {
Write-Warning "前回の処理で強制終了が発生しています。処理は続行されます。"
continue
}
# =============================================================================よろしければサポートお願いします。