Imports System.IO

Public Class usrCntlAuswertungenFiskal_IntrastatMeldung



    Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL

    Private Property rst As Object

    Private Sub usrCntlBrgBuchungenFremd_Load(sender As Object, e As EventArgs) Handles Me.Load
        datVon.Value = CDate("01." & Now.AddMonths(-1).Month & "." & Now.AddMonths(-1).Year)
        datBis.Value = datVon.Value.AddMonths(1).AddDays(-1)
    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim dt = getFiskal_Data()

            If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then

                If Not System.IO.Directory.Exists("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\") Then System.IO.Directory.CreateDirectory("\\FMZoll.verag.ost.dmn\FMZoll\ZM\")
                Dim fs As New FileStream("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\KA221.ASC", FileMode.Create)
                Dim sw As New StreamWriter(fs)

                ' File.AppendAllLines(Path.Combine(mydocpath,"WriteFile.txt"), lines);

                ' XML-Datei anlegen.
                '  fso = New FileSystemObject
                ' strPath = fso.BuildPath("\\FMZoll.verag.ost.dmn\FMZoll\ZM", "ZM_Formular_online.xml")
                ' txt = fso.CreateTextFile(strPath, True, False)
                'With txt

                Dim ATC_tmp = ""

                '  rst.MoveLast()
                '  Call SysCmd(acSysCmdInitMeter, "Fortschritt: ", rst.RecordCount)
                'rst.MoveFirst()
                'intZeile = 0
                ' Schleife zum Lesen der Fiskalvertretungsdaten.
                Dim zeile_m = 1
                For Each r As DataRow In dt.Rows
                    '  Inc intZeile  'addiert 1
                    ' Call SysCmd(acSysCmdUpdateMeter, intZeile)
                    If zeile_m < 1000 Then
                        ' Schreiben der Fiskalvertretungsdaten im XML-Format.
                        Dim Outputline = ""
                        Outputline &= "2" ' r("Versendung") 'Versendung
                        Outputline &= "2" ' r("Anmeldeform") 'Anmeldeform
                        Outputline &= " " 'Blank1
                        Outputline &= Format(Month(r("Bezugszeitraum")), "00") 'Anmeldemonat
                        Outputline &= "00" 'Null1
                        Outputline &= CInt(zeile_m).ToString("000000") 'Format(lngZlr, "000000") 'Paginiernummer
                        Outputline &= "  " 'Blank2
                        Outputline &= "09" '& r("BundeslandFinanzamt") 'BundeslandFinanzamt
                        Outputline &= "15314120202" 'r("Steuernummer") 'Steuernummer
                        Outputline &= "000" ' r("Unterscheidungsnummer") 'Unterscheidungsnummer
                        Outputline &= " " & r("Bestimmungsland") 'Bestimmungsland
                        Outputline &= "09" ' r("Ursprungsregion") 'Ursprungsregion
                        Outputline &= If(r("Geschäftsart") = "", "99", r("Geschäftsart")) 'Geschäftsart
                        Outputline &= r("Verkehrszweig") 'Verkehrszweig
                        Outputline &= " " 'Blank3
                        Outputline &= "      " 'Blank4
                        Outputline &= "      " 'Blank5
                        Outputline &= r("Warennummer")  'Warennummer
                        Outputline &= "   " 'Blank6
                        Outputline &= "  " 'Blank7
                        Outputline &= "     " 'Blank8
                        Outputline &= Format(r("Eigenmasse"), "00000000000") 'Eingenmasse
                        Outputline &= IIf(r("BesondereMasseinheit") = 0, "           ", Format(r("BesondereMasseinheit"), "00000000000")) 'BesondereMasseinheit
                        Outputline &= "  " 'Blank9
                        Outputline &= Format(r("Rechnungsbetrag"), "00000000000") 'Rechnungsbetrag
                        Outputline &= Format(r("StatistischerWert"), "00000000000") 'StatistischerWert
                        Outputline &= "  " 'Blank10
                        Outputline &= CDate(r("Bezugszeitraum")).ToString("MM") 'Bezugsmonat
                        Outputline &= CDate(r("Bezugszeitraum")).ToString("yy") 'Bezugsjahr
                        Outputline &= "2" 'Währungskennziffer
                        Outputline &= "     " 'Blank11
                        ATC_tmp = r("Registrierkennzeichen")


                        sw.WriteLine(Outputline)
                        zeile_m += 1
                    End If
                    ' DoEvents()
                    '   rst.MoveNext()
                Next
                '.Close()
                ' End With
                ' txt = Nothing
                ' fso = Nothing
                '   Me!strNachrichtenzeile = "Die XML-Datei finden Sie unter \\FMZoll.verag.ost.dmn\FMZoll\ZM\ZM_Formular_online.xml"

                ' rst.Close()
                ' rst = Nothing
                sw.Close()


                If Not Directory.Exists("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\ArchivNEU\") Then Directory.CreateDirectory("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\ArchivNEU\")

                Try
                    File.Copy("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\KA221.ASC", "\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\ArchivNEU\" & Now.ToString("dd.MM.yyyy HH.mm.ss") & " " & datVon.Value.ToShortDateString & " " & datBis.Value.ToShortDateString & " KA221.ASC")
                Catch ex As Exception
                    MsgBox(ex.Message & ex.StackTrace)
                End Try


                Process.Start("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\")
            End If

        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)
        End Try
        Me.Cursor = Cursors.Default

    End Sub


    Function getFiskal_Data() As DataTable
        Dim sqlStr = ""

        sqlStr &= "   SELECT 	  basman_nr as Mandant, basman_nl as Niederlassung, zbant_beznr as Bezugsnummer, zbpos_posnr as Positionsnummer, zbabg_regkz as Registrierkennzeichen,  Bestimmungsland, isnull(zbant_gesart,99) as Geschäftsart,  Verkehrszweig,  isnull(Warennummer,'00000000') as Warennummer, isnull(zbpos_eigm,'0') as Eigenmasse, zbpos_wmahst as BesondereMasseinheit,  CASE WHEN GesamtRechnungspreis>0  THEN CASE WHEN zbpos_posnr=1 THEN GesamtRechnungspreis ELSE 0 END ELSE zbapo_eustwe END AS Rechnungsbetrag, isnull(zbapo_zollw,'0') as StatistischerWert,  zbant_dsenda as Bezugszeitraum, zbpos_wabes as Warenbeschreibung,GesamtRechnungspreis "
        sqlStr &= "   FROM ( "

        If CheckBox2.Checked Then

            sqlStr &= "    SELECT zbant.basman_nr, zbant.basman_nl, zbant.zbant_beznr, zbpos.zbpos_posnr, zbapo.zbabg_regkz, case WHEN zbadz.baslnd_alpha is not null THEN zbadz.baslnd_alpha ELSE zbaaz.baslnd_alpha END as Bestimmungsland, zbant.zbant_gesart, Right([zbant_bfvkzg],1) AS Verkehrszweig, Left([zbpos_wanr],8) AS Warennummer, zbpos.zbpos_eigm, zbpos.zbpos_wmahst,  zbapo.zbapo_eustwe, zbapo.zbapo_zollw,  zbant.zbant_dsenda, zbpos.zbpos_wabes,0 as GesamtRechnungspreis "
            sqlStr &= "  FROM [192.168.133.98].[atlas].[dbo].[vwVERAG_Fiskal_zbant] as zbant"
            sqlStr &= "  INNER JOIN [192.168.133.98].atlas.dbo.vwVERAG_Fiskal_zbpos as zbpos ON zbant.[basman_nr]=zbpos.[basman_nr] AND zbant.[basman_nl]=zbpos.[basman_nl] AND zbant.[zbant_beznr]=zbpos.[zbant_beznr] AND zbant.[zbant_regnr]=zbpos.[zbant_regnr] AND zbant.[zbant_korant]=zbpos.[zbant_korant]"
            sqlStr &= " INNER JOIN [192.168.133.98].atlas.dbo.vwVERAG_Fiskal_zbapo as zbapo ON zbant.[basman_nr]=zbapo.[basman_nr] AND zbant.[basman_nl]=zbapo.[basman_nl] AND zbant.[zbant_beznr]=zbapo.[zbant_beznr] AND zbant.[zbant_regnr]=zbapo.[zbant_regnr] AND zbant.[zbant_korant]=zbapo.[zbant_korant] AND zbpos.zbpos_posnr=zbapo.zbapo_posnr"
            sqlStr &= " left outer JOIN [192.168.133.98].atlas.dbo.vwVERAG_Fiskal_zbadz as zbadz ON zbant.[basman_nr]=zbadz.[basman_nr] AND zbant.[basman_nl]=zbadz.[basman_nl] AND zbant.[zbant_beznr]=zbadz.[zbant_beznr] AND zbant.[zbant_regnr]=zbadz.[zbant_regnr] AND zbant.[zbant_korant]=zbadz.[zbant_korant]"
            sqlStr &= "  left outer JOIN [192.168.133.98].atlas.dbo.vwVERAG_Fiskal_zbaaz as zbaaz ON zbant.[basman_nr]=zbaaz.[basman_nr] AND zbant.[basman_nl]=zbaaz.[basman_nl] AND zbaaz.zbabg_regkz =zbapo.zbabg_regkz "
            sqlStr &= " WHERE zbant.zbant_dsenda Between '" & datVon.Value.ToShortDateString & "' And '" & datBis.Value.ToShortDateString & "' "

        End If


        If CheckBox2.Checked And (CheckBox1.Checked Or cbxDatenAusDakosyLaden.Checked) Then sqlStr &= " UNION"

        If CheckBox1.Checked Then

            sqlStr &= "   SELECT zbant.basman_nr, zbant.basman_nl, zbant.zbant_beznr, zbpos.zbpos_posnr, zbapo.zbabg_regkz, case WHEN zbadz.baslnd_alpha is not null THEN zbadz.baslnd_alpha ELSE zbaaz.baslnd_alpha END as Bestimmungsland, zbant.zbant_gesart, Right([zbant_bfvkzg],1) AS Verkehrszweig, Left([zbpos_wanr],8) AS Warennummer, zbpos.zbpos_eigm, zbpos.zbpos_wmahst,  zbapo.zbapo_eustwe, zbapo.zbapo_zollw,  zbant.zbant_dsenda, zbpos.zbpos_wabes,0 as GesamtRechnungspreis "
            sqlStr &= " FROM [VERAG-ATLAS1\SQLATLAS].[atlas].[dbo].[vwVERAG_Fiskal_zbant] as zbant"
            sqlStr &= " INNER JOIN [VERAG-ATLAS1\SQLATLAS].atlas.dbo.vwVERAG_Fiskal_zbpos as zbpos ON zbant.[basman_nr]=zbpos.[basman_nr] AND zbant.[basman_nl]=zbpos.[basman_nl] AND zbant.[zbant_beznr]=zbpos.[zbant_beznr] AND zbant.[zbant_regnr]=zbpos.[zbant_regnr] AND zbant.[zbant_korant]=zbpos.[zbant_korant]"
            sqlStr &= " INNER JOIN [VERAG-ATLAS1\SQLATLAS].atlas.dbo.vwVERAG_Fiskal_zbapo as zbapo ON zbant.[basman_nr]=zbapo.[basman_nr] AND zbant.[basman_nl]=zbapo.[basman_nl] AND zbant.[zbant_beznr]=zbapo.[zbant_beznr] AND zbant.[zbant_regnr]=zbapo.[zbant_regnr] AND zbant.[zbant_korant]=zbapo.[zbant_korant] AND zbpos.zbpos_posnr=zbapo.zbapo_posnr"
            sqlStr &= " left outer JOIN [VERAG-ATLAS1\SQLATLAS].atlas.dbo.vwVERAG_Fiskal_zbadz as zbadz ON zbant.[basman_nr]=zbadz.[basman_nr] AND zbant.[basman_nl]=zbadz.[basman_nl] AND zbant.[zbant_beznr]=zbadz.[zbant_beznr] AND zbant.[zbant_regnr]=zbadz.[zbant_regnr] AND zbant.[zbant_korant]=zbadz.[zbant_korant]"
            sqlStr &= "  left outer JOIN [VERAG-ATLAS1\SQLATLAS].atlas.dbo.vwVERAG_Fiskal_zbaaz as zbaaz ON zbant.[basman_nr]=zbaaz.[basman_nr] AND zbant.[basman_nl]=zbaaz.[basman_nl] AND zbaaz.zbabg_regkz =zbapo.zbabg_regkz "
            sqlStr &= " WHERE zbant.zbant_dsenda Between '" & datVon.Value.ToShortDateString & "' And '" & datBis.Value.ToShortDateString & "' "


        End If


        If (CheckBox2.Checked Or CheckBox1.Checked) And (cbxDatenAusDakosyLaden.Checked) Then sqlStr &= " UNION"



        If cbxDatenAusDakosyLaden.Checked Then

            sqlStr &= "   select replace(eza_firma,'VERAG','VERA') as basman_nr,eza_niederlassung as basman_nl,eza_ObjectName as zbant_beznr,ezaWP_PositionsNummer as zbpos_posnr,replace(eza_ANR,'ATC42','ATC0042') as zbabg_regkz,eza_Bestimmungsland as Bestimmungsland,eza_ArtGeschaeftCode as zbant_gesart,eza_VerkehrszweigGrenze as Verkehrszweig,lefT(ezaWP_WarennummerEZT,8) as Warennummer,ezaWP_Eigenmasse as zbpos_eigm,0 as zbpos_wmahst,0 as zbapo_eustwe,/*ezaWP_Zollwert*/[ezaWP_AHStatWert] as zbapo_zollw, CONVERT( date, (SELECT TOP 1 dySt_ergZeitpunkt FROM [tblDakosy_Statusmeldungen] WHERE [dySt_dyId]=eza_dyaAnmID AND  [dySt_status] between 60 and 70  order by  dySt_ergZeitpunkt DESC) ,104) as zbant_dsenda,ezaWP_Warenbezeichnung as zbpos_wabes,eza_Rechnungspreis as GesamtRechnungspreis"
            sqlStr &= " from tblDakosy_EZA inner join tblDakosy_EZA_Adressen ON ezaAd_EZAId=eza_Id"
            sqlStr &= "   inner join  tblDakosy_EZA_Warenposition on ezaWP_EzaId=eza_Id"
            '  sqlStr &= " WHERE (eza_Trans_DatumZeit) Between '" & datVon.Value.ToShortDateString & "' And '" & datBis.Value.ToShortDateString & "' "
            sqlStr &= " WHERE (SELECT COUNT(*) FROM [tblDakosy_Statusmeldungen] WHERE [dySt_dyId]=eza_dyaAnmID AND  [dySt_status] between 60 and 70  AND	CONVERT(date, [dySt_ergZeitpunkt] ,104 ) BETWEEN  '" & datVon.Value.ToShortDateString & "' And '" & datBis.Value.ToShortDateString & "'  )>0 "
            sqlStr &= "and ezaAd_AdressTyp='UC' and  /*eza_VerfahrenBeantragtCode LIKE '42%'  and */   eza_ANR LIKE 'ATC42%' and  [eza_UstIdAnmelder]='DE813667748' and ezaAd_UStIDAnmelder is not null and [eza_FinanzamtAnmelder]='9153' "
            ' sqlStr &= " group by eza_Id,eza_firma,eza_niederlassung,eza_ObjectName,eza_ANR,eza_Bestimmungsland,eza_ArtGeschaeftCode,eza_VerkehrszweigGrenze,eza_GesamtRohmasse,eza_Rechnungspreis,eza_Trans_DatumZeit "

        End If


        'sqlStr &= " UNION"
        sqlStr &= "    )"
        sqlStr &= " as Tb"
        sqlStr &= " ORDER BY Tb.basman_nr, Tb.basman_nl, Tb.zbant_beznr"

        '  sqlStr &= " SELECT Left([zbant_erwust],2) AS knre1, substring([zbant_erwust],3,len([zbant_erwust])) AS knre2"
        'sqlStr &= " ,(CASE WHEN [zbant_kurs]=0 THEN [zbant_preisr] ELSE  (([zbant_preisr]/[zbant_kurs])*100)/100  END) as betrag"
        ' sqlStr &= " FROM [VERAG-ATLAS1\SQLATLAS].atlas.dbo.zbant"
        ' sqlStr &= " WHERE (zbant_nadat) Between '" & datVon.Value.ToShortDateString & "' And '" & datBis.Value.ToShortDateString & "' "
        'sqlStr &= " AND zbant_stat IN(SELECT  [zbant_stat]  FROM [atlas_status]  where fiskal_freigabe=1)"
        'sqlStr &= " AND [zbant_erwust] <>''"

        Return SQL.loadDgvBySql(sqlStr, "FMZOLL", 600)

    End Function




    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        cProgramFunctions.genExcelFromDT_NEW(getFiskal_Data)

        Exit Sub
        Dim f As New frmTableVorschau
        f.MyDatagridview1.DataSource = getFiskal_Data()
        f.Show(Me)
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Process.Start("\\FMZoll.verag.ost.dmn\FMZoll\Intrastat\")
    End Sub
End Class
