Public Class usrCntlAuswertungenKunden_Auftragsrueckgang



    Dim SQL As New VERAG_PROG_ALLGEMEIN.SQL

    Private Sub usrCntlBrgBuchungenFremd_Load(sender As Object, e As EventArgs) Handles Me.Load
        cboMonatDurmaz.Items.Clear()
        cboMonatDurmaz.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("", ""))
        cboMonatDurmaz.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("Auftraggeber", "Vermittler"))
        cboMonatDurmaz.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("Empfänger", "Empfänger"))
        cboMonatDurmaz.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("Avisierer", "Avisierer"))
        cboMonatDurmaz.SelectedIndex = 1
        '  cboMonatDurmaz.Items.Add(New VERAG_PROG_ALLGEMEIN.MyListItem("", ""))
    End Sub

    Private Sub btnNCTSDurmaz_Click(sender As Object, e As EventArgs) Handles btnNCTSDurmaz.Click

        Try
            txtFilialen.ForeColor = Color.Black
            If txtFilialen.Text.Contains(",") Then
                For Each s In txtFilialen.Text.Split(",")
                    If Not IsNumeric(s) Then txtFilialen.ForeColor = Color.Red : Exit Sub
                Next
            Else
                If Not IsNumeric(txtFilialen.Text) Then txtFilialen.ForeColor = Color.Red : Exit Sub
            End If

            txtAbfArt.ForeColor = Color.Black
            If txtAbfArt.Text.Contains(",") Then
                For Each s In txtAbfArt.Text.Split(",")
                    If Not IsNumeric(s) Then txtAbfArt.ForeColor = Color.Red : Exit Sub
                Next
            Else
                If Not IsNumeric(txtAbfArt.Text) Then txtAbfArt.ForeColor = Color.Red : Exit Sub
            End If

            lblErr.Visible = False
            If cboMonatDurmaz._value = "" Then Exit Sub
            Dim sqlStr = ""
            sqlStr &= " select * FROM("
            sqlStr &= " select * ,"
            sqlStr &= " case when monatsdurchschn >" & txtSchwellenwertPeriode.Text & " then 'W'"
            sqlStr &= " 		else 'M'"
            sqlStr &= " 		end as DurschnBerechnung"
            sqlStr &= " FROM("
            sqlStr &= " SELECT  SUM (abfertigungsanzahl) as [Abf.Anzahl], VermittlerKundenNr, MAX(Vermittler) as Vermittler"
            sqlStr &= " 	  , cast(SUM (cast(abfertigungsanzahl as float) /12) as decimal(10,1) ) as Monatsdurchschn "
            sqlStr &= " 	  	  , (SELECT "
            sqlStr &= "         isnull(SUM(sp2.abfertigungsanzahl), 0)"
            sqlStr &= " 		 FROM [VERAG].[dbo].[Speditionsbuch] as sp2"

            sqlStr &= " 			  where sp2.Abfertigungsdatum between  cast(DATEADD (MONTH , -1 , GETDATE() ) as date)  AND GETDATE()"
            sqlStr &= " 			  and sp2.FilialenNr IN (" & txtFilialen.Text & ")"
            sqlStr &= "               and sp2.Abfertigungsart IN(" & txtAbfArt.Text & ") "
            sqlStr &= " 			  and sp2.VermittlerKundenNr=sp1.VermittlerKundenNr"
            sqlStr &= " 		) as LetzterMonat,"

            sqlStr &= " 		 (SELECT "
            sqlStr &= "         isnull(SUM(sp2.abfertigungsanzahl), 0)"
            sqlStr &= " 		 FROM [VERAG].[dbo].[Speditionsbuch] as sp2"
            sqlStr &= " 			  where sp2.Abfertigungsdatum between cast(DATEADD (WEEK , -1 , GETDATE() ) as date)  AND GETDATE()"
            sqlStr &= " 			  and sp2.FilialenNr IN (" & txtFilialen.Text & ")"
            sqlStr &= "               and sp2.Abfertigungsart IN(" & txtAbfArt.Text & ") "
            sqlStr &= " 			  and sp2.VermittlerKundenNr=sp1.VermittlerKundenNr"
            sqlStr &= " 		)  as LetzeWoche"

            sqlStr &= "   FROM [VERAG].[dbo].[Speditionsbuch] as  sp1"

            sqlStr &= "   where Abfertigungsdatum between cast(DATEADD (YEAR , -1 , GETDATE() ) as date)  AND GETDATE()"
            sqlStr &= "   and VermittlerKundenNr is not null  and VermittlerKundenNr > 0 "
            sqlStr &= "   and FilialenNr IN (" & txtFilialen.Text & ")"
            sqlStr &= "   and Abfertigungsart IN(" & txtAbfArt.Text & ") "

            sqlStr &= "   group by VermittlerKundenNr"
            sqlStr &= " 		) as T  where  [Abf.Anzahl]> " & txtAnz12Monate.Text & " "
            sqlStr &= " 		) as T2"
            sqlStr &= "         where"
            sqlStr &= " 	(DurschnBerechnung='M' and LetzterMonat<='" & txtletztePeriode.Text & "') or (DurschnBerechnung='W' and LetzeWoche<='" & txtletztePeriode.Text & "') "
            sqlStr &= "   order by [Abf.Anzahl] desc"

            sqlStr = sqlStr.Replace("Vermittler", cboMonatDurmaz._value)
            Dim dt As DataTable = SQL.loadDgvBySql(sqlStr, "FMZOLL")
            If dt Is Nothing OrElse dt.Rows.Count = 0 Then lblErr.Visible = True : Exit Sub

            cProgramFunctions.genExcelFromDT_NEW(dt) ', {"E1:E" & (dt.Rows.Count + 1)})

        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)
        End Try
    End Sub



End Class
