Imports System.Net.Mail
Imports System.IO

Partial Public Class MonthEnd
  Inherits System.Web.UI.Page

  Private Shared intDateColumn As Integer
  Private Shared dblRows As Double = 0
  Private Shared strGVFilter As String = ""
  Private Shared dr As DataRow
  Private db As New sqlDB

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then dblRows = 0

  End Sub

  Private Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete

    If Not Page.IsPostBack Then
      Dim arrFilter(0) As String
      intDateColumn = 0 'gvZZ.Columns.Count - 2
      'refPeriod.Text = Date.Now.ToString("yyyyMM")
      ddlPeriod.SelectedIndex = 0
      gvZZ.PageSize = 32
      'sdsX.SelectParameters.Item(0) = New System.Web.UI.WebControls.Parameter()
    End If

    dblRows = gvZZ.PageIndex * gvZZ.PageSize     '< has to run for every action				
  End Sub


  Protected Sub gvZZ_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gvZZ.Sorting
    sdsZ.FilterExpression = strGVFilter
    dblRows = 0
  End Sub

  Protected Sub gvZZ_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvZZ.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow Then

      Try
        'dblRows += 1
        'e.Row.Cells(0).Text = dblRows
        e.Row.Cells(intDateColumn).Text = sqlDB.SqlDate(e.Row.Cells(intDateColumn).Text & "0000", "d MMMM yyyy")  ' "d MMM yyyy HH:mm")
      Catch ex As Exception
        e.Row.Cells(intDateColumn).Text = "" 'Date.Now.ToString("n/a")
      End Try
    End If
  End Sub

  Protected Sub btnView_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnView.Click
    'gvZZ.DataSource = Nothing

    sdsZ.DataBind()
    'gvZZ.DataBind()

  End Sub

  Protected Sub btnProcess_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnProcess.Click
    Dim strPeriod As String = ddlPeriod.SelectedValue
    Dim strDate As String = Date.Today.ToString("yyyyMMdd")
    Dim strTemp As String = ""
    'bfbeEmails

    Dim ds As DataSet
    Dim dr As DataRow

    'sales month to date
    dr = db.GetRow("SELECT * FROM sysQuery WHERE strName = 'bfbe-Sales_Month_to_Date' ORDER BY intOrder")
    strTemp = dr!memQuery.ToString.Replace("20091001", strDate)
    ds = db.doQueryDS(strTemp.Replace("200910", strPeriod))

    'lblMessage.Text = strTemp.Replace("200910", strPeriod)
    'lblMessage.Text &= "<BR><BR>CreateCSVFile(ds.Tables(0), " & System.AppDomain.CurrentDomain.BaseDirectory() & "BFBE\ZA_" & strPeriod & "_INVW.txt, strDelimiter:=Chr(9), blnEnquote:=False)"

    CreateCSVFile(ds.Tables(0), System.AppDomain.CurrentDomain.BaseDirectory() & "BFBE\ZA_" & strPeriod & "_INVW.txt", strDelimiter:=Chr(9), blnEnquote:=False)

    'MailBrandon()

  End Sub

  Public Sub MailBrandon()
    Dim strPeriod As String = ddlPeriod.SelectedValue
    Dim strTo() As String
    Dim strTemp As String = ""
    'bfbeEmails

    Dim smtpC As New System.Net.Mail.SmtpClient("localhost") 'My.Settings.SMTPHost)
    Dim sales, customers As System.Net.Mail.Attachment
    Dim mm As MailMessage
    Dim ds As DataSet
    Dim dr As DataRow


    strTo = New String("pj@overdrive.co.za;william@overdrive.co.za").Split(";") '{} 'My.Settings.bfbeEmails.Split(";")

    For i As Integer = 1 To strTo.Length - 1

      Try
        mm = New MailMessage("superbowldaily@rgbc.co.za", strTo(i), "Superbowl Weekly [" & Date.Now.ToString("dd MMM yyyy") & "]", "")
        mm.IsBodyHtml = True

        Try
          sales = New Attachment(System.AppDomain.CurrentDomain.BaseDirectory() & "BFBE\ZA_" & ddlPeriod.SelectedValue & "_INVW.txt")
          mm.Attachments.Add(sales)
          sales.Dispose()
        Catch ex As Exception
        End Try

        Try
          customers = New Attachment(System.AppDomain.CurrentDomain.BaseDirectory() & "BFBE\ZA_" & ddlPeriod.SelectedValue & "_CUST.txt")
          mm.Attachments.Add(customers)
          customers.Dispose()
        Catch ex As Exception
        End Try

        smtpC.Send(mm)
        mm = Nothing

      Catch ex As Exception
        MsgBox(ex.Message & ex.StackTrace)

      End Try

    Next

    smtpC = Nothing
  End Sub

  Public Sub CreateCSVFile(ByVal dt As DataTable, ByVal strFilePath As String, Optional ByVal strDelimiter As String = ",", Optional ByVal blnEnquote As Boolean = True, Optional ByVal strEndOfLine As String = ControlChars.CrLf)
    Try
      Dim sw As New StreamWriter(strFilePath, False)

      'lblMessage.Text &= "<BR><BR>" & sw.ToString

      Dim strOutput, comma, quote As String
      Dim i, j As Integer

      comma = ""
      strOutput = ""
      If blnEnquote Then
        quote = """"
      Else
        quote = ""
      End If

      Try
        'HEADINGS
        For i = 0 To dt.Columns.Count - 1
          strOutput &= comma & quote & dt.Columns(i).ColumnName & quote
          comma = strDelimiter
        Next

        'DETAIL LINES
        For j = 0 To dt.Rows.Count - 1
          dr = dt.Rows(j)
          comma = ""                      'ini comma
          strOutput &= strEndOfLine  'last rec doesn't get chr(13)

          For i = 0 To dt.Columns.Count - 1
            Try
              dr(i) = db.Nz(dr(i), "")
            Catch ex As ArgumentException
              dr(i) = db.Nz(dr(i), 0)
            End Try
            strOutput &= comma & quote & dr(i).ToString & quote
            comma = strDelimiter
          Next

        Next

        'WRITE
        sw.Write(strOutput)
      Catch ex As Exception
        lblMessage.Text &= "<BR><BR>" & strOutput & ex.Message & ex.StackTrace
        sw.Write(strOutput & ex.Message & ex.StackTrace)
      Finally

        'CLOSE
        sw.Close()
      End Try

    Catch ex As Exception
      lblMessage.Text &= "<BR><BR>" & ex.Message & ex.StackTrace

    End Try

  End Sub

End Class