Imports Microsoft.Office.Interop

Public Class usrCntlSeriendruck

    Dim withPrint As Boolean = False

    Dim FMZOLL_SQL As New FMZOLL_SQL
    Private Sub btnWord_Click(sender As Object, e As EventArgs) Handles btnWord.Click
        If Not txtErstattungAbfNr.ForeColor = Color.DarkGreen Then
            frmMain.setInfo("info", "Die Abfertigungsnummer ist nicht gültig!", 2)
            Exit Sub
        End If
        If cbxErstSprache.Text = "" Then
            frmMain.setInfo("info", "Bitte wählen Sie eine Sprache!", 2)
            Exit Sub
        End If

        Dim WordDoc As Object
        Dim WordApp As Object
        WordApp = CreateObject("word.Application")
        WordDoc = CreateObject("word.Document")

        Dim oItem As VERAG_PROG_ALLGEMEIN.MyListItem = CType(cbxErstSprache.SelectedItem, VERAG_PROG_ALLGEMEIN.MyListItem)
        If Not My.Computer.FileSystem.DirectoryExists(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\") Then
            My.Computer.FileSystem.CreateDirectory(Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\")
        End If
        Dim strFileName As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\VERAG\tmp\tmp.doc"
        Try
            WordApp.Visible = (True) 'Word sichtbar
            WordDoc = WordApp.Documents.Open(AppDomain.CurrentDomain.BaseDirectory & "seriendruck\Vorlagen\Erstattungsschreiben\Erstattungsschreiben " & oItem.Value & ".dotx")
            WordDoc.Activate()

            Dim berichteSQL As New BerichteSQL
            Dim bericht As cErstasttungsschreiben = berichteSQL.getErstatung(txtErstattungAbfNrFiliale.Text, txtErstattungAbfNr.Text, txtErstattungAbfUnterNr.Text)
            'Dim ADRESSE = New VERAG_PROG_ALLGEMEIN.cAdressen(bericht.)
            If bericht.KorrAdresse <> "" Then
                bericht.KorrAdresse = bericht.KorrAdresse.Replace(Chr(13) & Chr(10), Chr(13))
                If InStr(bericht.KorrAdresse, Chr(13)) > 0 Then
                    Dim s = bericht.KorrAdresse.Split(Chr(13), Chr(10))
                    If s.Length > 0 Then WordDoc.FormFields("txtAdresseZeile1").Range.Text = s(0)
                    If s.Length > 1 Then WordDoc.FormFields("txtAdresseZeile2").Range.Text = s(1)
                    If s.Length > 2 Then WordDoc.FormFields("txtAdresseZeile3").Range.Text = s(2)
                    If s.Length > 3 Then WordDoc.FormFields("txtAdresseZeile4").Range.Text = s(3)
                    If s.Length > 4 Then WordDoc.FormFields("txtAdresseZeile5").Range.Text = s(4)
                    If s.Length > 5 Then WordDoc.FormFields("txtAdresseZeile6").Range.Text = s(5)
                    If s.Length > 6 Then WordDoc.FormFields("txtAdresseZeile7").Range.Text = s(6)
                End If
            Else
                WordDoc.FormFields("txtAdresseZeile1").Range.Text = bericht.Name.Trim
                WordDoc.FormFields("txtAdresseZeile2").Range.Text = bericht.Straße
                WordDoc.FormFields("txtAdresseZeile3").Range.Text = bericht.LandKz & " " & bericht.Ort
            End If

            WordDoc.FormFields("Datum").Range.Text = bericht.MWST
            WordDoc.FormFields("Betrag").Range.Text = String.Format("{0:C}", CDbl(bericht.Betrag))

            WordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone
            Try : WordDoc.SaveAs(strFileName) : Catch : End Try

            If withPrint Then
                WordDoc.PrintOut()
                WordDoc.Close(False)
                WordDoc = Nothing
                WordApp.Quit()
                Me.BringToFront()
                txtErstattungAbfNr.Focus()
                txtErstattungAbfNr.SelectAll()
            End If
            'WordDoc.PrintOut()
            'WordDoc.PrintOut()
            'WordDoc.Close()
            ' WordDoc = Nothing
            ' WordDoc.Quit()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        withPrint = False
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnShowData.Click
        If Not txtErstattungAbfNr.ForeColor = Color.DarkGreen Then Exit Sub
        If cbxErstSprache.Text = "" Then Exit Sub

        frmShowData.Show()
        frmShowData.DataGridView1.DataSource = FMZOLL_SQL.loadDgvBySqlFMZOLLREAL("exec spSELECTUStVErstattungsschreiben " & txtErstattungAbfNrFiliale.Text & ", " & txtErstattungAbfNr.Text & ", " & txtErstattungAbfUnterNr.Text & " ")
        frmShowData.Label2.Text = "Anzahl: " & frmShowData.DataGridView1.RowCount
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click


        Dim von As DateTime = Now
        Dim bis As DateTime = Now.AddMonths(2)

        frmShowData.Show()
        'LandKz='RO' AND 
        'MsgBox("SELECT * FROM vwUStSerienbriefStNrGueltigkeitsdatum WHERE StNrGültigkeitsdatum BETWEEN '" & von.ToShortDateString & "' AND '" & bis.ToShortDateString & "' GROUP BY [Name 1],[Name 2],PLZ,StraßePostfach,Postfach,Straße,LandKz,Ort,[StNrGültigkeitsdatum],AdressenNr ORDER BY PLZ, AdressenNr ")
        frmShowData.DataGridView1.DataSource = FMZOLL_SQL.loadDgvBySql("SELECT * FROM vwUStSerienbriefStNrGueltigkeitsdatum WHERE StNrGültigkeitsdatum BETWEEN '" & von.ToShortDateString & "' AND '" & bis.ToShortDateString & "' GROUP BY [Name 1],[Name 2],PLZ,StraßePostfach,Postfach,Straße,LandKz,Ort,[StNrGültigkeitsdatum],AdressenNr ORDER BY PLZ, AdressenNr ")
        frmShowData.Label2.Text = "Anzahl: " & frmShowData.DataGridView1.RowCount


        Exit Sub


        Dim sprache As String = "DE"
        Select Case cbxErstSprache.SelectedIndex
            Case 0 : sprache = "DE"
            Case 1 : sprache = "RO"
            Case 2 : sprache = "TR"
            Case 3 : sprache = "YU"
        End Select

        Dim WordApp As Word.Application
        Dim WordDoc As Word.Document
        '  Dim SQLSttmnt As String = "Select * From rechner WHERE ID=5360"
        WordApp = New Word.Application()

        WordDoc = WordApp.Documents.Open(AppDomain.CurrentDomain.BaseDirectory & "\seriendruck\Vorlagen\Erstattungsschreiben\Erstattungsschreiben " & sprache & ".doc")
        WordDoc.MailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters

        Dim sqlstr As String = "SELECT * FROM vwUStSerienbriefStNrGueltigkeitsdatum WHERE LandKz='RO' AND StNrGültigkeitsdatum BETWEEN '" & von.ToShortDateString & "' AND '" & bis.ToShortDateString & "' GROUP BY [Name 1],StraßePostfach,PLZ ORDER BY PLZ, AdressenNr "

        WordDoc.MailMerge.OpenDataSource(Name:="", _
              Connection:="DSN=dbConn_FMZOLL;DATABASE=VERAG;uid=sa;pwd=BmWr501956;", _
              SQLStatement:=sqlstr, SubType:=Microsoft.Office.Interop.Word.WdMergeSubType.wdMergeSubTypeWord2000)

        'Provider=SQLOLEDB;Server=DEVELOPER\DEVSQL;Database=VERAG;uid=sa;Password=BmWr501956;
        '  With WordDoc.MailMerge
        '  '.Destination = Word.WdMailMergeDestination.wdSendToPrinter
        '  .SuppressBlankLines = True
        '  With .DataSource
        ' .FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
        '.LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
        ' End With
        ' .Execute(Pause:=False)
        ' End With
        ' WordDoc.Close()
        WordApp.Visible = True
    End Sub

    Private Sub txtErstattungAbfNr_KeyDown(sender As Object, e As KeyEventArgs) Handles txtErstattungAbfNr.KeyDown, txtErstattungAbfNrFiliale.KeyDown, txtErstattungAbfUnterNr.KeyDown, cbxErstSprache.KeyDown
        If e.KeyCode = Keys.Enter Then
            withPrint = cbxSofortDruck.Checked
            btnWord.PerformClick()
        End If

    End Sub

    Private Sub txtErstattungAbfNr_LostFocus(sender As Object, e As EventArgs) Handles txtErstattungAbfNr.TextChanged
        txtErstattungAbfNr.BackColor = Color.White
        txtErstattungAbfNr.ForeColor = Color.Red
        If NurZahlen(txtErstattungAbfNr.Text) And NurZahlen(txtErstattungAbfNrFiliale.Text) And NurZahlen(txtErstattungAbfUnterNr.Text) Then
            'Dim dt As DataTable = FMZOLL_SQL.loadDgvBySqlFMZOLLREAL("SELECT TOP 1 * FROM vwUStVErstattungsschreiben WHERE AbfertigungsNr=" & txtErstattungAbfNr.Text & " and FilialenNr =" & txtErstattungAbfNrFiliale.Text & " and UnterNr =" & txtErstattungAbfUnterNr.Text & " ")

            Dim dt As DataTable = FMZOLL_SQL.loadDgvBySqlFMZOLLREAL("exec spSELECTUStVErstattungsschreiben " & txtErstattungAbfNrFiliale.Text & ", " & txtErstattungAbfNr.Text & ", " & txtErstattungAbfUnterNr.Text & " ")

            If Not dt Is Nothing AndAlso dt.Rows.Count > 0 Then
                Try
                    Select Case dt.Rows(0).Item("LandKz").ToString
                        Case "AT", "DE", "CH" : cbxErstSprache.SelectedIndex = 0
                        Case "RO" : cbxErstSprache.SelectedIndex = 1
                        Case "TR" : cbxErstSprache.SelectedIndex = 2
                        Case Else : cbxErstSprache.SelectedIndex = 3
                    End Select
                Catch : End Try
                txtErstattungAbfNr.BackColor = Color.White
                txtErstattungAbfNr.ForeColor = Color.DarkGreen
            End If
        End If
    End Sub


    Private Sub btnWordPrint_Click(sender As Object, e As EventArgs) Handles btnWordPrint.Click
        withPrint = True
        btnWord.PerformClick()

    End Sub

    Private Sub usrCntlSeriendruck_Load(sender As Object, e As EventArgs) Handles Me.Load
        cbxErstSprache.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("DEUTSCH", "DE"))
        cbxErstSprache.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("RUMÄNISCH", "RO"))
        cbxErstSprache.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("TÜRKISCH", "TR"))
        cbxErstSprache.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("YU", "YU"))
        cbxErstSprache.SelectedIndex = 0
        txtErstattungAbfNr.Focus()
    End Sub
    Public Function NurZahlen(ByVal Text As String) As Boolean
        If Text.Length = 0 Then Return False
        If Not IsNumeric(Text) Then Return False

        For i As Integer = 0 To Text.Length - 1
            If Not "0123456789".Contains(Text.Chars(i)) Then
                Return False
            End If
        Next
        Return True
    End Function

    Private Sub txtErstattungAbfUnterNr_LostFocus(sender As Object, e As EventArgs) Handles txtErstattungAbfUnterNr.LostFocus
        If NurZahlen(txtErstattungAbfUnterNr.Text) Then
            If txtErstattungAbfUnterNr.Text.Length = 1 Then txtErstattungAbfUnterNr.Text = "0" & txtErstattungAbfUnterNr.Text
        End If
    End Sub


End Class
