DataUtils

Imports System.Data.SqlClient
Imports System.Reflection

Module DataUtils
''' <summary>
''' Statusのマスタ値をデータテーブルで取得
''' </summary>
''' <param name="AddBlankRow">先頭にブランク行を追加する場合True</param>
''' <returns></returns>
Public Function GetEnumStatus(ByVal Optional AddBlankRow As Boolean = False) As TData(Of TEnumMaster)
Return GetEnumMaster(AppConstants.EM_TYPE_STATUS, AddBlankRow)
End Function
''' <summary>
''' UserGrantのマスタ値をデータテーブルで取得
''' </summary>
''' <param name="AddBlankRow">先頭にブランク行を追加する場合True</param>
''' <returns></returns>
Public Function GetEnumUserGrant(ByVal Optional AddBlankRow As Boolean = False) As TData(Of TEnumMaster)
Return GetEnumMaster(AppConstants.EM_TYPE_USER_GRANT, AddBlankRow)
End Function
''' <summary>
''' Family(続柄)のマスタ値をデータテーブルで取得
''' </summary>
''' <param name="AddBlankRow">先頭にブランク行を追加する場合True</param>
''' <returns></returns>
Public Function GetEnumFamily(ByVal Optional AddBlankRow As Boolean = False) As TData(Of TEnumMaster)
Return GetEnumMaster(AppConstants.EM_TYPE_FAMILY, AddBlankRow)
End Function
Private Function GetEnumMaster(ByVal masterType As String, ByVal Optional AddBlankRow As Boolean = False) As TData(Of TEnumMaster)
Dim tEnm As New TEnumMaster
Dim tdEnm As New TDEnumMaster(tEnm)
tEnm.MasterType = masterType
tEnm.Status = AppConstants.E_STATUS.Active
tdEnm.Execute()
If AddBlankRow Then
tEnm = New TEnumMaster
tdEnm.TList.Insert(tEnm, 0)
End If
Return tdEnm.TList
End Function
''' <summary>
''' 続柄のテキスト値をコード値より取得
''' </summary>
''' <param name="code"></param>
''' <returns></returns>
Public Function GetTsuzukiGaraTextByCode(ByVal code As Integer) As String
GetTsuzukiGaraTextByCode = GetEnumFamily().DList.Where(Function(x) x.MasterValue = code).Select(Function(x) x.MasterText).First
End Function

''' <summary>
''' 郵便番号をキーに住所を取得
''' </summary>
''' <param name="zipcode"></param>
''' <returns></returns>
Public Function GetAddressByZipCode(ByVal zipcode As String) As TAddress
If String.IsNullOrWhiteSpace(zipcode) Then Return Nothing
Dim tAdr As New TAddress With {
.ZipNo = Replace(zipcode, STR_HF, String.Empty)
}
Dim tdAdr As New TDAddress(tAdr)
tdAdr.Execute()
If tdAdr.count = NO_VALUE Then Return Nothing
Return tdAdr.DataLists(SINGLE_RECORD)
End Function

''' <summary>
''' 県の一覧を取得
''' </summary>
''' <param name="AddBlankRow"></param>
''' <returns></returns>
Public Function GetKenList(ByVal Optional AddBlankRow As Boolean = False) As TData(Of TKen)
Dim tdKen As New TDKen()
tdKen.Execute()
If AddBlankRow Then
tdKen.TList.Insert(New TKen, 0)
End If
Return tdKen.TList
End Function

''' <summary>
''' ステータスコンボボックスにデータセットをセット
''' </summary>
''' <param name="cmb"></param>
Public Sub SetStatusToComboBox(ByVal cmb As ComboBox)
Dim dtSt As List(Of TEnumMaster) = DataUtils.GetEnumStatus(True).DList
cmb.DataSource = dtSt
cmb.ValueMember = NameOf(TEnumMaster.MasterValue).ToString
cmb.DisplayMember = NameOf(TEnumMaster.MasterText).ToString
End Sub

''' <summary>
''' IF FileのヘッダレイアウトをXMLから取得
''' </summary>
''' <param name="xDoc"></param>
''' <returns></returns>
Public Function GetCheckXMLHeaderValues(ByVal xDoc As XDocument) As List(Of String)
Return xDoc.Elements("mappings").Elements("mapping").Select(Function(x) x.Attribute("ColumnName").Value).ToList
End Function

''' <summary>
''' Dictionary(Key,Item(IEnumerable))をTCassと同じレイアウトのDataTableに変換する
''' Dictionaryのデータ列順は、テーブルの列順と同じである前提
''' </summary>
''' <typeparam name="T1">Dictirnary Key</typeparam>
''' <typeparam name="T2">Dictionary Value String()等</typeparam>
''' <typeparam name="T3">TClass</typeparam>
''' <param name="dic"></param>
''' <param name="tClass"></param>
''' <returns></returns>
Public Function ConvertDataTableByDicrionary(Of T1, T2 As IEnumerable, T3 As New)(ByVal dic As Dictionary(Of T1, T2), tClass As T3) As DataTable
'---TClassのPropertyNameに沿った列を持つDataTableを生成
Dim wrkTable As New DataTable
For Each wrk As PropertyInfo In tClass.GetType().GetProperties()
wrkTable.Columns.Add(wrk.Name, wrk.PropertyType)
Next
'---DataTableにデータを挿入
For Each wrkDic In dic
Dim row = wrkTable.NewRow
Dim i As Integer = 0
For Each wrk As PropertyInfo In tClass.GetType().GetProperties()
row(wrk.Name) = wrkDic.Value(i)
i += 1
Next
wrkTable.Rows.Add(row)
Next
Return wrkTable
End Function

Public Sub CreateCmbImportTask(ByVal cmb As BaseComboBox, ByVal Optional tskCategory As Integer = NO_VALUE, ByVal Optional AddOridinalRow As E_ORDINAL_ROW = E_ORDINAL_ROW.None)
Dim tCls As New TImportTaskCatgory
If tskCategory <> NO_VALUE Then tCls.CategoryId = tskCategory
Dim tdCls As New TDImportTaskCategory(tCls)
tdCls.Execute()
Select Case AddOridinalRow
Case E_ORDINAL_ROW.None
tdCls.TList.Insert(New TImportTaskCatgory, 0)
Case E_ORDINAL_ROW.All
tdCls.TList.Insert(New TImportTaskCatgory With {.CategoryName = STR_ALL}, 0)
End Select
cmb.DataSource = tdCls.DataLists
cmb.ValueMember = NameOf(tCls.CategoryId).ToString
cmb.DisplayMember = NameOf(tCls.CategoryName).ToString
End Sub

''' <summary>
''' App.Configに記載している暗号化済みの接続文字列から、複合化接続文字列を取得
''' </summary>
''' <param name="baseString">暗号化済み接続文字列</param>
''' <returns></returns>
Public Function CreateConnectionString(ByVal Optional baseString As String = "") As String
Dim sUtil As New SecurityUtils
If baseString = String.Empty Then baseString = GetConfigParameters.GetMSSQLConnectString
Dim sb As New SqlConnectionStringBuilder(baseString)
Dim tAesUser As New TAESSet With {
.Salt = AppConstants.SEC_SQSLT,
.Key = sUtil.CreateKey(AppConstants.SEC_SQL_PASS_PHARSE, .Salt),
.Iv = AppConstants.SEC_SQIV,
.EncryptedText = sb.UserID,
.KeyCreatePassPharse = AppConstants.SEC_SQL_PASS_PHARSE
}
Dim tAesPass As New TAESSet With {
.Salt = AppConstants.SEC_SQSLT,
.Key = sUtil.CreateKey(AppConstants.SEC_SQL_PASS_PHARSE, .Salt),
.Iv = AppConstants.SEC_SQIV,
.EncryptedText = sb.Password,
.KeyCreatePassPharse = AppConstants.SEC_SQL_PASS_PHARSE
}
sb.UserID = sUtil.Decrypt(tAesUser).DecryptedText
sb.Password = sUtil.Decrypt(tAesPass).DecryptedText
Return sb.ToString
End Function

''' <summary>
''' 新規パスワードを暗号化済みの状態で作成する
''' </summary>
''' <returns></returns>
Public Function CreateEncryptedNewPassword() As ITAESSet
Dim sUtils As New SecurityUtils
Dim tAes As New TAESSet With {
.DecryptedText = Utils.GetRndString(12),
.KeyCreatePassPharse = AppConstants.SEC_SQL_PASS_PHARSE
}
Return sUtils.Encrypt(tAes)
End Function

''' <summary>
''' 指定の文字列を暗号化し、IV、SALTと併せて取得する
''' </summary>
''' <param name="tAes"></param>
''' <returns></returns>
Public Function CreateEncryptedString(ByVal tAes As ITAESSet) As ITAESSet
Dim sUtils As New SecurityUtils
Return sUtils.Encrypt(tAes)
End Function

''' <summary>
''' パスワードを復号する
''' </summary>
''' <returns></returns>
Public Function CreateDecyptedString(ByVal tAes As ITAESSet) As ITAESSet
Dim sUtils As New SecurityUtils
Return sUtils.Decrypt(tAes)
End Function

''' <summary>
''' UserIdと平文のパスから、そのUserのSaltとIVに対応した暗号化済みパスを取得
''' </summary>
''' <param name="tUser"></param>
''' <returns></returns>
Public Function CreateEncryptedUPasswordByUserId(ByVal tUser As TUserInfo) As String
Dim tdUser As TDUserInfo = TDUserFactory.CreateTD(New TUserInfo With {.UserId = tUser.UserId})
tdUser.Execute()
If tdUser.count = NO_VALUE Then Return String.Empty
Dim wrkUser As TUserInfo = tdUser.DataLists(SINGLE_RECORD)
Dim sUtil As New SecurityUtils
Dim tAes As New TAESSet With {
.Iv = wrkUser.UserIv,
.Salt = wrkUser.UserSalt,
.DecryptedText = tUser.UserPassword
}
Return sUtil.Encrypt(tAes)?.EncryptedText
End Function
End Module