Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms

Public Class frmMain
    Dim SQLHeader As New SQLConnector
    Dim SQLFooter As New SQLConnector

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            dpDateFrom.Value = Now
            dpDateTo.Value = Now

            With System.Deployment.Application.ApplicationDeployment.CurrentDeployment.CurrentVersion
                Me.Text = "BSBC Invoice Export V" & .Major & "." & .Minor & "." & .Build & "." & .Revision & " (25 June 2018)"
            End With
        Catch ex As Exception
        End Try

        With My.Settings

            edtSQLServer.Text = .SQLServer

            edtFTPServer.Text = .FTPServer
            edtFTPUsername.Text = .FTPUsername
            edtFTPPassword.Text = .FTPPassword

            txtPath.Text = .XMLPath
            If .SQLServer <> "" Then
                SQLHeader.SetConnectionString(.SQLServer, .SQLUsername, .SQLPassword, "BSBC")
                SQLFooter.SetConnectionString(.SQLServer, .SQLUsername, .SQLPassword, "BSBC")
            End If

            Log("Settings loaded.", Color.Black)

        End With

    End Sub
    Private Sub cmdSaveXML_Click(sender As Object, e As EventArgs) 

        With My.Settings
            .XMLUsername = edtXMLUsername.Text
            .XMLPassword = edtXMLPassword.Text
        End With
        My.Settings.Save()
        MsgBox("XML settings saved.", vbOKOnly + vbInformation, "Saved")

    End Sub

    Private Sub cmdSaveSQL_Click(sender As Object, e As EventArgs) Handles cmdSaveSQL.Click

        With My.Settings
            .SQLServer = edtSQLServer.Text
        End With
        My.Settings.Save()
        MsgBox("SQL settings saved.", vbOKOnly + vbInformation, "Saved")

    End Sub

    Private Sub cmdSaveFTP_Click(sender As Object, e As EventArgs) Handles cmdSaveFTP.Click

        With My.Settings
            .FTPServer = edtFTPServer.Text
            .FTPUsername = edtFTPUsername.Text
            .FTPPassword = edtFTPPassword.Text
        End With
        My.Settings.Save()
        MsgBox("FTP settings saved.", vbOKOnly + vbInformation, "Saved")

    End Sub


    Private Sub cmdProcess_Click(sender As Object, e As EventArgs) Handles cmdProcess.Click
        Dim XMLPath As String = My.Settings.XMLPath

        If My.Settings.XMLPath = "" Then
            MsgBox("Please set a directory in settings where XML archives and conversion can be done first.", vbOKOnly + vbCritical)
            Exit Sub
        End If

        If SQLHeader.QueryExecuterIsIdle And chkUploadOnly.Checked = False Then

            Log("PROCESS STARTED", Color.Black)
            Log("Connecting to SQL and Fetching requested data...", Color.Black)

            SQLHeader.RunQuery("EXEC [SUN_GETINVOICES] @EFFECTIVE_FROM='" & Format(dpDateFrom.Value, "yyyy-MM-dd") & "', @EFFECTIVE_TO='" & Format(dpDateTo.Value, "yyyy-MM-dd") & "'")

            Do Until SQLHeader.QueryExecuterIsIdle AndAlso SQLHeader.QueryHasCompleted
                Application.DoEvents()
            Loop

            Dim ObjectCount As Integer
            ObjectCount = SQLHeader.DataBuffer.Tables(0).Rows.Count

            Log("Found " & ObjectCount & " object/s", Color.Black)

            For i = 0 To ObjectCount - 1
                Dim sInvoiceNumber As String = SQLHeader.DataBuffer.Tables(0).Rows(i).Item("Reference")
                Dim sAccountLink As String = SQLHeader.DataBuffer.Tables(0).Rows(i).Item("AccountLink")


                Log("Creating Files " & i + 1 & " of " & ObjectCount, Color.Black)

                Dim FileName As String = sInvoiceNumber & ".csv"
                Dim sPath As String = ""
                Dim sFTPPath As String = ""
                '5261 - Checkers
                '6960 - PNP
                If sAccountLink = "5261" Then
                    If Not Directory.Exists(XMLPath & "\SHOPRITE") Then
                        Directory.CreateDirectory(XMLPath & "\SHOPRITE")
                    End If
                    sPath = XMLPath & "\SHOPRITE\" & FileName
                    sFTPPath = "ftp://BLUESKY@xpliquor.dyndns.org/ShopriteInvoices/" & FileName
                ElseIf sAccountLink = "6960" Then
                    If Not Directory.Exists(XMLPath & "\PNP") Then
                        Directory.CreateDirectory(XMLPath & "\PNP")
                    End If

                    sPath = XMLPath & "\PNP\" & FileName
                    sFTPPath = "ftp://BLUESKY@xpliquor.dyndns.org/Invoices/" & FileName
                End If

                Dim td As DataTable = GetInvoiceDetails(sInvoiceNumber)

                Dim sString As String = ""
                For x As Integer = 0 To td.Rows.Count - 1
                    sString = sString & td.Rows(x)(0).ToString & "|"
                    sString = sString & td.Rows(x)(1).ToString & "|"
                    sString = sString & td.Rows(x)(2).ToString & "|"
                    sString = sString & td.Rows(x)(3).ToString & "|"
                    sString = sString & td.Rows(x)(4).ToString & "|"
                    sString = sString & td.Rows(x)(5).ToString & "|"
                    sString = sString & td.Rows(x)(6).ToString & "|"
                    sString = sString & td.Rows(x)(7).ToString & "|"
                    sString = sString & td.Rows(x)(8).ToString & "|"
                    sString = sString & td.Rows(x)(9).ToString & "|"
                    sString = sString & td.Rows(x)(10).ToString & "|"
                    sString = sString & td.Rows(x)(11).ToString & "|"
                    sString = sString & td.Rows(x)(12).ToString & "|"
                    sString = sString & td.Rows(x)(13).ToString & "|"
                    sString = sString & td.Rows(x)(14).ToString & "|"
                    sString = sString & td.Rows(x)(15).ToString & "|"
                    sString = sString & td.Rows(x)(16).ToString & "|"
                    sString = sString & td.Rows(x)(17).ToString & "|"
                    sString = sString & td.Rows(x)(18).ToString & "|"
                    sString = sString & td.Rows(x)(19).ToString & "|"
                    sString = sString & td.Rows(x)(20).ToString & "|"
                    sString = sString & td.Rows(x)(21).ToString & "|"
                    sString = sString & td.Rows(x)(22).ToString & "|"

                    sString = sString & Environment.NewLine
                Next

                sString = sString & "|"

                Dim file As System.IO.StreamWriter
                file = My.Computer.FileSystem.OpenTextFileWriter(sPath, True)
                file.Write(sString)
                file.Close()

                FTPFile(sPath, sFTPPath)

            Next
        Else
            MsgBox("A SQL request is still open. please wait or restart the application.")
        End If

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        FBDialog.ShowDialog()

        If FBDialog.SelectedPath <> "" Then
            txtPath.Text = FBDialog.SelectedPath
            My.Settings.XMLPath = FBDialog.SelectedPath
            My.Settings.Save()
        End If
    End Sub

    Function GetInvoiceDetails(sInvoiceNumber As String) As DataTable
        If SQLFooter.QueryExecuterIsIdle Then

            Log("PROCESS STARTED", Color.Black)
            Log("Connecting to SQL and Fetching requested data...", Color.Black)

            SQLFooter.RunQuery("EXEC [SUN_GETINVOICEDETAILS] @Reference='" & sInvoiceNumber & "'")
        End If

        Do Until SQLFooter.QueryExecuterIsIdle AndAlso SQLFooter.QueryHasCompleted
            Application.DoEvents()
        Loop

        Return SQLFooter.DataBuffer.Tables(0)
    End Function

    Private Sub FTPFile(sFileName As String, sFTPPath As String)

        Try
            ' set up request...
            Dim clsRequest As System.Net.FtpWebRequest = DirectCast(System.Net.WebRequest.Create(sFTPPath), System.Net.FtpWebRequest)
            clsRequest.Credentials = New System.Net.NetworkCredential("BLUESKY", "2uHQg8GZv3")
            clsRequest.Method = System.Net.WebRequestMethods.Ftp.UploadFile
            Dim file() As Byte = System.IO.File.ReadAllBytes(sFileName)

            ' upload file...
            Dim clsStream As System.IO.Stream = clsRequest.GetRequestStream()
            clsStream.Write(file, 0, file.Length)
            clsStream.Close()
            clsStream.Dispose()

            Log("Upload Done... ", Color.Green)

            Try
                IO.File.Delete(sFileName)
                Log("File deleted... ", Color.Green)
            Catch ex As Exception
                Log("File not deleted..." & ex.Message, Color.Red)
            End Try

        Catch ex As Exception
            Log("Upload Failed..." & ex.Message, Color.Red)
        End Try
    End Sub

End Class