Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Imports System.Data.SqlClient
- Imports System.Text
- Module ModuleMain
- Private sqlCon As SqlConnection
- Sub Main()
- '02.01, 01.04, 01.07 и 01.10 sendFile
- Dim day As Integer = DateTime.Today.Day
- Dim month As Integer = DateTime.Today.Month
- Dim xml = XDocument.Load("Config.xml")
- Dim date1 As String = xml.<config>.<Dates>.<date1>.Value
- Dim strArraydate1() As String
- strArraydate1 = Split(date1, ".")
- Dim day1 As String = Convert.ToInt32(strArraydate1(0))
- Dim month1 As Integer = Convert.ToInt32(strArraydate1(1))
- Dim date2 As String = xml.<config>.<Dates>.<date2>.Value
- Dim strArraydate2() As String
- strArraydate2 = Split(date2, ".")
- Dim day2 As String = Convert.ToInt32(strArraydate2(0))
- Dim month2 As Integer = Convert.ToInt32(strArraydate2(1))
- Dim date3 As String = xml.<config>.<Dates>.<date3>.Value
- Dim strArraydate3() As String
- strArraydate3 = Split(date3, ".")
- Dim day3 As String = Convert.ToInt32(strArraydate3(0))
- Dim month3 As Integer = Convert.ToInt32(strArraydate3(1))
- Dim date4 As String = xml.<config>.<Dates>.<date4>.Value
- Dim strArraydate4() As String
- strArraydate4 = Split(date4, ".")
- Dim day4 As String = Convert.ToInt32(strArraydate4(0))
- Dim month4 As Integer = Convert.ToInt32(strArraydate4(1))
- If (day = day1 And month = month1) Or (day = day2 And month = month2) Or (day = day3 And month = month3) Or (day = day4 And month = month4) Then
- Console.WriteLine(String.Format("Sending= {0}.{1}.{2}", day.ToString, month.ToString, DateTime.Today.Year.ToString()))
- SendExcelFile()
- Else
- Console.WriteLine(String.Format("Not sent - Dates for send: " + date1 + ", " + date2 + ", " + date3 + " and " + date4 + " "))
- End If
- End Sub
- Sub SendExcelFile()
- If My.Computer.FileSystem.FileExists("Config.xml") Then
- Dim xml = XDocument.Load("Config.xml")
- Dim strConn As String = xml.<config>.<dbconfig>.<dbpath>.Value
- sqlCon = New SqlConnection(strConn)
- Using (sqlCon)
- Dim sqlComm As New SqlCommand
- sqlComm.Connection = sqlCon
- sqlComm.CommandText = "proc_SearchProcedure"
- sqlComm.CommandType = CommandType.StoredProcedure
- sqlComm.Parameters.AddWithValue("idProc", "")
- sqlComm.Parameters.AddWithValue("nameProc", "")
- sqlComm.Parameters.AddWithValue("nameProcEng", "")
- sqlComm.Parameters.AddWithValue("english", "0")
- sqlComm.Parameters.AddWithValue("noDecision", "")
- sqlComm.Parameters.AddWithValue("dateActivationStart", "")
- sqlComm.Parameters.AddWithValue("dateActivationEnd", "")
- sqlComm.Parameters.AddWithValue("dateReviseStart", "")
- sqlComm.Parameters.AddWithValue("dateReviseEnd", "")
- sqlComm.Parameters.AddWithValue("responsibleSector", "0")
- sqlComm.Parameters.AddWithValue("tbSectorProcedureidSector", "0")
- sqlComm.Parameters.AddWithValue("tbProceduresidStatus", "0")
- sqlComm.Parameters.AddWithValue("tbProceduresidType", "0")
- sqlComm.Parameters.AddWithValue("tbProceduresidConf", "0")
- sqlComm.Parameters.AddWithValue("reportNotModified", "True")
- sqlComm.Parameters.AddWithValue("involvedSector", "0")
- sqlCon.Open()
- sqlComm.ExecuteNonQuery()
- Dim ds As DataSet = Nothing
- Dim da As SqlDataAdapter = Nothing
- da = New SqlDataAdapter
- da.SelectCommand = DirectCast(sqlComm, SqlCommand)
- ds = New DataSet
- da.Fill(ds)
- Dim tab As String = vbTab
- Dim sb As New StringBuilder()
- sb.AppendLine("<html>")
- sb.AppendLine("<meta http-equiv=""Content-Language"" content=""mk""> ")
- sb.AppendLine("<meta http-equiv=""Content-Type"" content=""text/html; charset=UTF-8""> ")
- sb.AppendLine(tab & "<body>")
- sb.AppendLine(tab & tab & "<table rules=""all"" border=""1"">")
- Dim listSkip As New List(Of Integer)
- Dim noSkipColumn As Integer = 0
- ' headers.
- sb.Append(tab & tab & tab & "<tr>")
- For Each dc As DataColumn In ds.Tables(0).Columns
- Dim headerColumn As String = dc.ColumnName
- Select Case dc.ColumnName
- Case "idProc"
- headerColumn = "ИД"
- Case "name"
- headerColumn = "Име"
- Case "nameEng"
- headerColumn = "Име анг."
- Case "noDecision"
- headerColumn = "Бр. одлука"
- Case "dateActivation"
- headerColumn = "Датум на одобрување"
- Case "dateRevise"
- headerColumn = "Ревидирано на"
- Case "responsibleSector"
- headerColumn = "Надлежен сектор"
- Case "statusName"
- headerColumn = "Статус"
- Case "nameConf"
- headerColumn = "Право на пристап"
- Case "changeRequestFrom"
- headerColumn = "Изменето од"
- Case "revisedRequestFrom"
- headerColumn = "Ревидирано од"
- Case "notes"
- headerColumn = "Забелешки"
- Case Else
- listSkip.Add(noSkipColumn)
- End Select
- If Not listSkip.Contains(noSkipColumn) Then
- sb.AppendFormat("<th scope=""col"">{0}</th>", headerColumn)
- End If
- noSkipColumn = noSkipColumn + 1
- Next
- sb.AppendLine("</tr>")
- ' data rows
- For Each dr As DataRow In ds.Tables(0).Rows
- sb.Append(tab & tab & tab & "<tr>")
- noSkipColumn = 0
- For Each dc As DataColumn In ds.Tables(0).Columns
- If Not listSkip.Contains(noSkipColumn) Then
- Dim cellValue As String = If(dr(dc) IsNot Nothing, dr(dc).ToString(), "")
- sb.AppendFormat("<td>{0}</td>", cellValue)
- End If
- noSkipColumn = noSkipColumn + 1
- Next
- sb.AppendLine("</tr>")
- Next
- sb.AppendLine(tab & tab & "</table>")
- sb.AppendLine(tab & "</body>")
- sb.AppendLine("</html>")
- Dim path As String = xml.<config>.<FolderPath>.<path>.Value
- Dim renderedDataSet As String = sb.ToString()
- System.IO.File.WriteAllText(path + "SBProcedures_" + DateTime.Today.ToString("dd_MM_yyyy") + ".xls", renderedDataSet)
- MailNotification.SendMailNotification()
- Console.WriteLine(String.Format("Sending completed"))
- End Using
- End If
- End Sub
- End Module
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement