Imports System.Text
Imports CrystalDecisions.CrystalReports
Imports CrystalDecisions.CrystalReports.Engine

Imports System.Drawing.Printing
Imports CrystalDecisions.Shared

Public Class frMain
  Public db As DBConnection

  Public Sub ExportInvoicesToPDF()
    Dim dt As DataTable
    Dim dtInvoices As DataTable
    Dim strSQL As String
    Dim path As String
    strSQL = "SELECT strInvoiceMessage FROM tSystem"
    dt = db.Query(strSQL)
    Dim strMessage As String = dt.Rows(0)!strInvoiceMessage
    strSQL = "SELECT tmpInvoice.*, tOwner.strAccountsEmail " & _
              "FROM tmpInvoice " & _
              "INNER JOIN tOwner ON tmpInvoice.refOwnerID = tOwner.OwnerIR ORDER BY tmpInvoice.InvoiceIR "
    dtInvoices = db.Query(strSQL)
    For Each drInvoices In dtInvoices.Rows
      Label1.TextAlign = ContentAlignment.MiddleCenter
      Label1.Text = "Processing Invoice " & drInvoices!lngInvoiceNo
      Application.DoEvents()
      Dim Report As New crInvoice
      strSQL = "SELECT (SELECT tCompany.strName FROM tCompany) AS strName, (SELECT tCompany.strLogoPath FROM tCompany) AS strLogoPath, (SELECT  [tCompany.strPostAddress1] & '  ' & [tCompany.strPostAddress2] & '  ' & [tCompany.strPostAddress3] & '  ' & [tCompany.strPostAddress4] & '  ' & [tCompany.strPostAddress5] FROM tCompany) AS PostalAddress, (SELECT tCompany.strPhone1 FROM tCompany) AS strPhone1, " & _
                      "(SELECT tCompany.strFax FROM tCompany) AS strFax, (SELECT tCompany.strCell FROM tCompany) AS strCell, (SELECT tCompany.strEmail FROM tCompany) AS strEmail, (SELECT tCompany.strVATNo FROM tCompany) AS strVatNo, tmpInvoice.lngInvoiceNo, tmpInvoice.dblOpeningBalance, tmpInvoice.dteInvoiceDate, tmpInvoiceItem.strHorseName, tmpInvoiceItem.dteDate, tmpInvoiceItem.strDescription, tmpInvoiceItem.dblDebit, " & _
                      "tmpInvoiceItem.dblCredit, tmpInvoiceItem.strVatIndicator, tmpInvoice.dblClosingBalance, tmpInvoice.dblNinety, tmpInvoice.dblSixty, tmpInvoice.dblThirty, tmpInvoice.dblCurrent, tmpInvoice.strPostAddress1 , tmpInvoice.strPostAddress2, tmpInvoice.strPostAddress3, tmpInvoice.strPostAddress4, tmpInvoice.strPostAddress5, tmpInvoice.refOwnerID, tmpInvoice.strVATNoOwner, [strTitle] & '. ' & [strInitials] & ' ' & [strSurname] AS OwnerName, " & _
                      "tmpInvoiceItem.InvoiceItemIR, [strSurname] & ', ' & [strTitle] & '. ' & [strFName] & '  (' & tmpInvoice.refOwnerID & ')' AS Owner, tmpInvoice.dblVExcl, tmpInvoice.dblVVat, tmpInvoice.dblNV, tmpInvoice.dblTotal " & _
                      "FROM tmpInvoiceItem RIGHT JOIN tmpInvoice ON tmpInvoiceItem.refInvoiceID = tmpInvoice.InvoiceIR " & _
                      "WHERE tmpInvoice.InvoiceIR = " & drInvoices!InvoiceIR
      dt = db.Query(strSQL)
      Report.SetDataSource(dt)
      Report.SetParameterValue("pMessage", strMessage)
      Dim exportOpts As New ExportOptions()
      Dim diskOpts As New DiskFileDestinationOptions()
      exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
      exportOpts = Report.ExportOptions
      exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat
      exportOpts.ExportDestinationType = ExportDestinationType.DiskFile
      'Dim aString As String = System.DateTime.Now.Year & "-" & DateTime.Now.Month.ToString.PadLeft(2, "0") & "-" & DateTime.Now.Day.ToString.PadLeft(2, "0")
      Dim aString As String = drInvoices!dteInvoiceDate.ToString.Replace("/", "-")
      'aString = Replace(X, "/", "")
      'Dim ExportFileName As String = "c:\Report001_" & OW_DatabaseName & aString & "_" & cStores.Code & ".pdf"
      Dim strEmail As String = drInvoices!strAccountsEmail
      If strEmail = "" Then
        path = System.AppDomain.CurrentDomain.BaseDirectory & "Invoices\" & aString & "\Not Emailed\"
      Else
        path = System.AppDomain.CurrentDomain.BaseDirectory & "Invoices\" & aString & "\Emailed\"
      End If
      Dim ExportFileName As String = path & drInvoices!strSurname & " " & takeOutIllegalCharacters(drInvoices!strFName) & " " & aString & ".pdf"
      If (Not System.IO.Directory.Exists(path)) Then
        System.IO.Directory.CreateDirectory(path)
      End If
      diskOpts.DiskFileName = ExportFileName
      exportOpts.DestinationOptions = diskOpts
      Report.Export()
      Dim strBody As String = "Dear " & drInvoices!strFName & " " & drInvoices!strSurname & ", " & "<br>" & "<br>" & "Find your invoice attached." & "<br>" & "<br>" & "<br>" & "Regards," & "<br>" & dt.Rows(0)!strName
      If strEmail.Contains("@") Then
        'sendMail(ExportFileName, drInvoices!strAccountsEmail, dt.Rows(0)!strName & " Invoice " & aString, strBody)
        Dim arrEmailAddresses = Split(drInvoices!strAccountsEmail, ";")
        For Each strEmailAddress As String In arrEmailAddresses
          sendMail(ExportFileName, strEmailAddress, dt.Rows(0)!strName & " Invoice " & aString, strBody)
        Next
      End If
      Report.Dispose()
      Report = Nothing
      dt.Clear()
      dt = Nothing
    Next
  End Sub

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'For Each arg As String In Environment.GetCommandLineArgs()
    '  MsgBox(arg)
    'Next arg    
    Label1.Text = "Working..."
    Button1.Enabled = False
    ExportInvoicesToPDF()
    Button1.Enabled = True
    Label1.Text = "Done"
  End Sub

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim ArgsArray() As String = Environment.GetCommandLineArgs()
    If ArgsArray.Length > 1 Then
      If ArgsArray(1) = "dfgh" Then
        enableApp()
      Else
        disableApp()
      End If
    Else
      disableApp()
    End If
    enableApp() 'uncomment for debugging
  End Sub

  Private Sub enableApp()
    Button1.Enabled = True
    Label1.Text = "Ready"
    db = New DBConnection()
    db.Connect()
    displayDetails()
  End Sub

  Private Sub disableApp()
    Button1.Enabled = False
    Label1.Text = "This application must be called from the Ascot Stable Managment Application to work."
  End Sub

  Private Sub sendMail(ByVal filename As String, ByVal strEmail As String, ByVal strSubject As String, ByVal strBody As String)
    Label1.Text = "Sending Email"
    System.Windows.Forms.Application.DoEvents()
    Try
      'Created Outlook Application object 
      Dim OLApp As New Outlook.Application
      'Creating outlook Namespace object 
      Dim OLNameSpace As Outlook.NameSpace
      OLNameSpace = OLApp.GetNamespace("MAPI")
      'creating outlook mailitem 
      Dim msg As Outlook.MailItem
      'creating newblan mail message 
      msg = OLApp.CreateItem(Outlook.OlItemType.olMailItem)
      'Get outboxfolder
      Dim oOutboxFolder As Outlook.MAPIFolder
      oOutboxFolder = OLNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderOutbox)
      msg.Recipients.Add(strEmail)
      'adding subject information to the mail message 
      msg.Subject = strSubject
      'adding body message information to the mail message 
      msg.HTMLBody = strBody
      'Add attachment
      msg.Attachments.Add(filename)
      msg.Send()
    Catch ex As Exception
      System.Windows.Forms.MessageBox.Show(Me, "Error sending mail, " & ex.Message & ", Please send file manualy, " & filename)
    End Try
  End Sub

  Public Sub displayDetails()
    Label1.TextAlign = ContentAlignment.MiddleLeft
    Dim strSQL As String
    Dim dt As DataTable
    strSQL = "Select  (SELECT Count(InvoiceIR) FROM tmpInvoice INNER JOIN tOwner ON tOwner.OwnerIR = tmpInvoice.refOwnerID WHERE strAccountsEmail NOT LIKE '') as CountWithEmail, " & _
              "       (SELECT Count(InvoiceIR) FROM tmpInvoice INNER JOIN tOwner ON tOwner.OwnerIR = tmpInvoice.refOwnerID WHERE strAccountsEmail LIKE '') as CountWithoutEmail, " & _
              "       Max(dteInvoiceDate) as strDate FROM tmpInvoice"
    dt = db.Query(strSQL)
    Dim aString As String = dt.Rows(0)!strDate.ToString.Replace("/", "-")
    Label1.Text = "Clicking the export button will export " & dt.Rows(0)!CountWithEmail + dt.Rows(0)!CountWithoutEmail & " invoices. " & vbCrLf & vbCrLf & _
                  "Of these, " & dt.Rows(0)!CountWithEmail & " have email addresses associated and will be emailed and saved to the following folder: " & vbCrLf & _
                  System.AppDomain.CurrentDomain.BaseDirectory & "Invoices\" & aString & "\Emailed\ " & vbCrLf & vbCrLf & _
                  "The remaining " & dt.Rows(0)!CountWithoutEmail & " will be saved to: " & vbCrLf & _
                  System.AppDomain.CurrentDomain.BaseDirectory & "Invoices\" & aString & "\Not Emailed\ "
  End Sub

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Me.Close()
  End Sub

  Private Function takeOutIllegalCharacters(ByVal inString As String) As String
    inString = Replace(inString, "\", "")
    inString = Replace(inString, "/", "")
    inString = Replace(inString, """", "")
    inString = Replace(inString, "'", "")
    inString = Replace(inString, "<", "")
    inString = Replace(inString, ">", "")
    inString = Replace(inString, "|", "")
    inString = Replace(inString, ":", "")
    inString = Replace(inString, ";", "")
    inString = Replace(inString, "?", "")
    Return inString
  End Function
End Class