Imports Microsoft.VisualBasic
Imports System.Data
Imports AppVariable
Imports System.IO
Imports System.Web.UI.Page
Imports AppVariable_QA
Imports AppMail
Imports System.Net
Imports AppCommon


Public Class AppQA

    Shared DBServerAuthUserId As String = ConfigurationManager.AppSettings("WebDavAuthUserId").ToString()
    Shared DBServerAuthUserPass As String = ConfigurationManager.AppSettings("WebDavAuthUserPass").ToString()

    Shared Function 新規問合せ登録(ByVal str登録者区分 As String,
                       ByVal str登録者CD As String,
                       ByVal strTo履修年度 As String,
                       ByVal strTo成績年度 As String,
                       ByVal strTo分野CD As String,
                       ByVal strTo科目CD As String,
                       ByVal strTo講座CD As String,
                       ByVal str件名 As String,
                       ByVal str問合せ内容 As String,
                       ByVal str問合せ先区分 As String,
                       ByVal str問合せ先CD As String,
                            fileId As String) As Integer

        Dim retNewID As Integer

        Using spm As New StoredProcedureManager("QA_新規問合せ_INSERT")
            spm.AddPrmIn("@登録者区分", Data.SqlDbType.VarChar, 20, str登録者区分)
            spm.AddPrmIn("@登録者CD", Data.SqlDbType.VarChar, 20, str登録者CD)
            spm.AddPrmIn("@履修年度", Data.SqlDbType.SmallInt, strTo履修年度)
            spm.AddPrmIn("@成績年度", Data.SqlDbType.SmallInt, strTo成績年度)
            spm.AddPrmIn("@分野CD", Data.SqlDbType.VarChar, 20, strTo分野CD)
            spm.AddPrmIn("@科目CD", Data.SqlDbType.VarChar, 20, strTo科目CD)
            spm.AddPrmIn("@講座CD", Data.SqlDbType.VarChar, 20, strTo講座CD)
            spm.AddPrmIn("@件名", Data.SqlDbType.VarChar, 200, IIf(String.IsNullOrEmpty(str件名), DBNull.Value, str件名))
            spm.AddPrmIn("@問合せ内容", Data.SqlDbType.VarChar, -1, str問合せ内容)
            spm.AddPrmIn("@問合せ先区分", Data.SqlDbType.VarChar, 20, IIf(String.IsNullOrEmpty(str問合せ先区分), DBNull.Value, str問合せ先区分))
            spm.AddPrmIn("@問合せ先CD", Data.SqlDbType.VarChar, 20, IIf(String.IsNullOrEmpty(str問合せ先CD), DBNull.Value, str問合せ先CD))
            Dim guidValue As Guid
            If Guid.TryParse(fileId, guidValue) Then
                spm.AddPrmIn("@添付ファイルID", Data.SqlDbType.UniqueIdentifier, guidValue)
            Else
                spm.AddPrmIn("@添付ファイルID", Data.SqlDbType.UniqueIdentifier, DBNull.Value)
            End If
            spm.AddPrmOut("@out_問合せID", Data.SqlDbType.Int)

            spm.Exec()
            retNewID = spm.GetValue("@out_問合せID")

        End Using

        Return retNewID

    End Function

    Shared Function 問合せSUB_INSERT(ByVal int問合せID As Integer,
                       ByVal strFrom区分 As String,
                       ByVal strFromCD As String,
                       ByVal strTo区分 As String,
                       ByVal strToCD As String,
                       ByVal str返信内容 As String,
                             fileId As String) As Integer

        Using spm As New StoredProcedureManager("QA_問合せSUB_INSERT")
            spm.AddPrmIn("@問合せID", Data.SqlDbType.SmallInt, int問合せID)
            spm.AddPrmIn("@From区分", Data.SqlDbType.VarChar, 20, strFrom区分)
            spm.AddPrmIn("@FromCD", Data.SqlDbType.VarChar, 20, strFromCD)
            spm.AddPrmIn("@To区分", Data.SqlDbType.VarChar, 20, strTo区分)
            spm.AddPrmIn("@ToCD", Data.SqlDbType.VarChar, 20, strToCD)
            spm.AddPrmIn("@返信内容", Data.SqlDbType.NVarChar, -1, str返信内容)
            spm.AddPrmOut("@out_返信ID", Data.SqlDbType.SmallInt)

            Dim guidValue As Guid
            If Guid.TryParse(fileId, guidValue) Then
                spm.AddPrmIn("@添付ファイルID", Data.SqlDbType.UniqueIdentifier, guidValue)
            Else
                spm.AddPrmIn("@添付ファイルID", Data.SqlDbType.UniqueIdentifier, DBNull.Value)
            End If
            spm.Exec()

            Dim int返信ID = spm.GetValue("@out_返信ID")

            Return int返信ID

        End Using
    End Function

    Shared Function Get問合せ詳細(ByVal str問合せID As String) As DataTable
        Using spm As New StoredProcedureManager("QA_問合せ詳細_SELECT")
            spm.AddPrmIn("@問合せID", Data.SqlDbType.Int, IIf(String.IsNullOrEmpty(str問合せID), 0, str問合せID))

            spm.Fill("Contact")

            Return spm.ds.Tables("Contact")

        End Using
    End Function

    Shared Sub 宛先追加(strTo区分 As String,
                    strTo履修年度 As String,
                    strTo成績年度 As String,
                    strTo分野CD As String,
                    strTo科目CD As String,
                    strTo講座CD As String)

        Using spm As New StoredProcedureManager("QA_TMP問合せ宛先_INSERT")
            spm.AddPrmIn("@PORTALID", Data.SqlDbType.VarChar, 200, HttpContext.Current.Session(S_COM_PortalID))
            spm.AddPrmIn("@To区分", Data.SqlDbType.VarChar, 20, strTo区分)
            spm.AddPrmIn("@To履修年度", Data.SqlDbType.SmallInt, strTo履修年度)
            spm.AddPrmIn("@To成績年度", Data.SqlDbType.SmallInt, strTo成績年度)
            spm.AddPrmIn("@To分野CD", Data.SqlDbType.VarChar, 20, strTo分野CD)
            spm.AddPrmIn("@To科目CD", Data.SqlDbType.VarChar, 20, strTo科目CD)
            spm.AddPrmIn("@To講座CD", Data.SqlDbType.VarChar, 20, strTo講座CD)
            spm.Exec()
        End Using

    End Sub

    Shared Sub 宛先削除(strTo区分 As String,
                    strTo履修年度 As String,
                    strTo成績年度 As String,
                    strTo分野CD As String,
                    strTo科目CD As String,
                    strTo講座CD As String,
                     str処理区分 As String)

        Using spm As New StoredProcedureManager("QA_TMP問合せ宛先_DELETE")
            spm.AddPrmIn("@PORTALID", Data.SqlDbType.VarChar, 200, HttpContext.Current.Session(S_COM_PortalID))
            spm.AddPrmIn("@To区分", Data.SqlDbType.VarChar, 20, strTo区分)
            spm.AddPrmIn("@To履修年度", Data.SqlDbType.SmallInt, strTo履修年度)
            spm.AddPrmIn("@To成績年度", Data.SqlDbType.SmallInt, strTo成績年度)
            spm.AddPrmIn("@To分野CD", Data.SqlDbType.VarChar, 20, strTo分野CD)
            spm.AddPrmIn("@To科目CD", Data.SqlDbType.VarChar, 20, strTo科目CD)
            spm.AddPrmIn("@To講座CD", Data.SqlDbType.VarChar, 20, strTo講座CD)

            spm.AddPrmIn("@処理区分", Data.SqlDbType.VarChar, 20, str処理区分)
            spm.Exec()
        End Using

    End Sub


    Shared Sub 問合せ添付追加(id As String,
                fileName As String,
                filePath As String
              )

        '基幹側で参照できるように、URL形式のパスは絶対パスに変換する
        If filePath.StartsWith("http://") OrElse filePath.StartsWith("https://") Then
            Dim rootUrlPath As String = Com_Get_変数("PT_COMMON_DIRECTORY")
            Dim rootfilePath As String = Com_Get_変数("COMMON_DIRECTORY")
            filePath = filePath.Replace(rootUrlPath, rootfilePath)
            filePath = filePath.Replace("/", "\")
        End If

        Using spm As New StoredProcedureManager("QA_問合せ添付_INSERT")

            spm.AddPrmIn("@ファイルID", Data.SqlDbType.UniqueIdentifier, Guid.Parse(id))
            spm.AddPrmIn("@元ファイル名", Data.SqlDbType.NVarChar, 255, fileName)
            spm.AddPrmIn("@ファイルパス", Data.SqlDbType.NVarChar, 255, filePath)
            spm.AddPrmIn("@登録日", Data.SqlDbType.DateTime, Now)
            spm.AddPrmIn("@PORTALID", Data.SqlDbType.VarChar, 200, HttpContext.Current.Session(S_COM_PortalID))
            spm.Exec()
        End Using

    End Sub

    Shared Function ShowFileName(fileName As String) As String
        ' ファイル名が長い場合は、最初の10文字と最後の5文字+拡張子を表示
        Dim extension As String = System.IO.Path.GetExtension(fileName)
        Dim fileNameWithoutExtension As String = System.IO.Path.GetFileNameWithoutExtension(fileName)

        If fileNameWithoutExtension.Length > 15 Then
            Dim firstPart As String = fileNameWithoutExtension.Substring(0, 10)
            Dim lastPart As String = fileNameWithoutExtension.Substring(fileNameWithoutExtension.Length - 5)
            Return firstPart & "..." & lastPart & extension
        Else
            Return fileName
        End If
    End Function

    Shared Function Get申込詳細(userCd As String) As DataTable
        Dim dt As DataTable
        Using spm As New StoredProcedureManager("QA_申込詳細_GET")
            spm.AddPrmIn("@学籍NO", SqlDbType.VarChar, userCd)
            spm.Fill()

            dt = spm.ds.Tables(0)
        End Using

        Return dt

    End Function

    Shared Function CombineUrlPaths(baseUrl As String, fileName As String) As String
        Return baseUrl.TrimEnd("/") & "/" & fileName
    End Function

    Shared Sub UploadFileToRemoteServer(localFilePath As String, remoteUrl As String)
        Using client As New WebClient()
            ' 認証
            Dim username As String = DBServerAuthUserId
            Dim password As String = DBServerAuthUserPass
            Dim credentials As String = Convert.ToBase64String(Encoding.ASCII.GetBytes(username & ":" & password))
            client.Headers("Authorization") = "Basic " & credentials

            Dim encodeUrl As String = AppQA.EncodeUrl(remoteUrl)
            ' ファイルをアップロード
            client.UploadFile(encodeUrl, "PUT", localFilePath)
        End Using
    End Sub

    Shared Function EncodeUrl(url As String) As String
        ' URLをパースして各部分を取得
        Dim uri As New Uri(url)

        ' スキーム（http:// や https://）とホスト名は変更しない
        Dim scheme As String = uri.Scheme
        Dim host As String = uri.Host

        ' パスとクエリ文字列をエンコード
        Dim encodedPath As String = String.Join("/", uri.Segments.Select(Function(s) HttpUtility.UrlEncode(s.TrimEnd("/"c))))
        Dim encodedQuery As String = If(String.IsNullOrEmpty(uri.Query), "", "?" & HttpUtility.UrlEncode(uri.Query.TrimStart("?"c)))

        ' エンコードされたURLを組み立てる（StringFormat形式）
        Return String.Format("{0}://{1}/{2}{3}", scheme, host, encodedPath, encodedQuery)
    End Function

End Class
