Form1.Visible = True

Form1.Activate()

End Sub

Private Sub Form4_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form1.Visible = False

End Sub

Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

End Sub

Private Function MinDate() As Date

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "select open_ticket from ticket where id_ticket = (select MIN(id_ticket) from ticket)"

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

MinDate = DataSet2.Tables.Item(0).Rows.Item(0).Item(0)

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged

If RadioButton1.Checked Then

RadioButton1.ForeColor = Color.Black

DateTimePicker1.Enabled = False

DateTimePicker2.Enabled = False

Label1.ForeColor = Color.DarkGray

Label2.ForeColor = Color.DarkGray

Label3.ForeColor = Color.DarkGray

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton1.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged

If RadioButton2.Checked Then

RadioButton2.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton2.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged

If RadioButton3.Checked Then

RadioButton3.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton3.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton4.CheckedChanged

If RadioButton4.Checked Then

RadioButton4.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton4.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton5_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton5.CheckedChanged

If RadioButton5.Checked Then

RadioButton5.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton5.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton6_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton6.CheckedChanged

If RadioButton6.Checked Then

RadioButton6.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton6.ForeColor = Color.DarkGray

End If

End Sub

Private Sub RadioButton7_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton7.CheckedChanged

If RadioButton7.Checked Then

RadioButton7.ForeColor = Color.Black

DateTimePicker1.Enabled = True

DateTimePicker2.Enabled = True

Label1.ForeColor = Color.Black

Label2.ForeColor = Color.Black

Label3.ForeColor = Color.Black

Dim d As Date = MinDate()

DateTimePicker1.Value = d

DateTimePicker1.MinDate = d

DateTimePicker2.MinDate = d

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

Else

RadioButton7.ForeColor = Color.DarkGray

End If

End Sub

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If RadioButton1.Checked Then

Otchet_Nevyp()

End If

If RadioButton2.Checked Then

Otchet_Vyp()

End If

If RadioButton3.Checked Then

Otchet_PoIsp()

End If

If RadioButton4.Checked Then

Otchet_Otkl()

End If

If RadioButton5.Checked Then

Otchet_ProblUsers()

End If

If RadioButton6.Checked Then

Otchet_ProblPC()

End If

If RadioButton7.Checked Then

Otchet_Dynamic()

End If

End Sub

Private Sub Otchet_Nevyp()

Dim StartDate As String = Form6.GetDateString(DateTimePicker1.Value)

Dim EndDate As String = Form6.GetDateString(DateTimePicker2.Value)

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter1 As New SqlClient.SqlDataAdapter

Dim DataSet1 As New DataSet

Dim SqlConnection1 As New SqlClient.SqlConnection

SqlConnection1.ConnectionString = con_string

Dim SqlCommand1 As New SqlClient.SqlCommand

SqlCommand1.CommandText = "select [id_ticket],[fio],[id_incident] from [ticket]" & vbCrLf & _

"where [id_status] < 5 and [otklon] = 0 " & vbCrLf & _

"and [open_ticket] >= convert(datetime,'" & StartDate & "',12) and [open_ticket] <= convert(datetime,'" & EndDate & "',12)"

SqlAdapter1.SelectCommand = SqlCommand1

SqlAdapter1.SelectCommand.Connection = SqlConnection1

Dim c_row, c_col As Integer

Dim id_ticket, fio, incident, comment As String

Try

Dim oWord As Word.Application

Dim oDoc As Word.Document

Dim oTable As Word.Table

Dim oPara1, oPara2, oPara3, oPara4 As Word.Paragraph

oWord = CreateObject("Word.Application")

oWord.Visible = True

oDoc = oWord.Documents.Add

oPara1 = oDoc.Content.Paragraphs.Add

oPara1.Range.Text = "Отчет о невыполненных заявках - " & DateAndTime.Now.ToString

oPara1.Range.Font.Size = 13

oPara1.Range.Font.Bold = True

oPara1.Range.InsertParagraphAfter()

SqlAdapter1.SelectCommand.Connection.Open()

SqlAdapter1.Fill(DataSet1)

c_row = DataSet1.Tables.Item(0).Rows.Count

c_col = DataSet1.Tables.Item(0).Columns.Count

oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, c_row + 1, 4)

oTable.Range.ParagraphFormat.SpaceAfter = 6

oTable.Range.Font.Size = 11

oTable.Range.Font.Bold = False

oTable.Rows.Borders.Enable = True

oTable.Columns.Borders.Enable = True

oTable.Columns(1).SetWidth(43, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(2).SetWidth(80, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(3).SetWidth(170, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(4).SetWidth(179, Word.WdRulerStyle.wdAdjustNone)

oTable.Rows.Item(1).Range.Font.Bold = True

oTable.Rows.Item(1).Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15

oTable.Cell(1, 1).Range.Text = "Номер"

oTable.Cell(1, 2).Range.Text = "Пользователь"

oTable.Cell(1, 3).Range.Text = "Инцидент"

oTable.Cell(1, 4).Range.Text = "Текст заявки"

For i = 1 To c_row

id_ticket = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(0)

fio = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(1)

incident = GetIncident(DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(2))

comment = GetComment(id_ticket)

oTable.Cell(i + 1, 1).Range.Text = CStr(id_ticket)

oTable.Cell(i + 1, 2).Range.Text = CStr(fio)

oTable.Cell(i + 1, 3).Range.Text = CStr(incident)

oTable.Cell(i + 1, 4).Range.Text = Replace(CStr(comment), "----Пользователь----" & vbCrLf, "")

Next

SqlAdapter1.SelectCommand.Connection.Close()

oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "select COUNT(*) from ticket where [id_status] < 5 and [otklon] = 0"

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

Dim nz As String = DataSet2.Tables.Item(0).Rows.Item(0).Item(0).ToString

SqlAdapter2.SelectCommand.Connection.Close()

oPara2.Range.ParagraphFormat.SpaceBefore = 0

oPara2.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara2.Range.ParagraphFormat.SpaceAfter = 0

oPara2.Range.ParagraphFormat.SpaceAfterAuto = False

oPara2.Range.Text = "Всего заявок - " & vbTab & vbTab & vbTab & nz

oPara2.Range.Font.Size = 11

oPara2.Range.Font.Bold = True

oPara2.Range.InsertParagraphAfter()

oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter3 As New SqlClient.SqlDataAdapter

Dim DataSet3 As New DataSet

Dim SqlConnection3 As New SqlClient.SqlConnection

cmd = "select distinct [id_incident] from [ticket] where [id_status] < 5 and [otklon] = 0"

SqlConnection3.ConnectionString = con_string

Dim SqlCommand3 As New SqlClient.SqlCommand

SqlCommand3.CommandText = cmd

SqlAdapter3.SelectCommand = SqlCommand3

SqlAdapter3.SelectCommand.Connection = SqlConnection3

SqlAdapter3.SelectCommand.Connection.Open()

SqlAdapter3.Fill(DataSet3)

Dim ninc As String = DataSet3.Tables.Item(0).Rows.Count.ToString

SqlAdapter3.SelectCommand.Connection.Close()

oPara3.Range.ParagraphFormat.SpaceBefore = 0

oPara3.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara3.Range.ParagraphFormat.SpaceAfter = 0

oPara3.Range.ParagraphFormat.SpaceAfterAuto = False

oPara3.Range.Text = "Всего типов инцидентов - " & vbTab & ninc

oPara3.Range.Font.Size = 11

oPara3.Range.Font.Bold = True

oPara3.Range.InsertParagraphAfter()

oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter4 As New SqlClient.SqlDataAdapter

Dim DataSet4 As New DataSet

Dim SqlConnection4 As New SqlClient.SqlConnection

cmd = "select distinct convert(char(11),[fio]) from [ticket] where [id_status] < 5 and [otklon] = 0"

SqlConnection4.ConnectionString = con_string

Dim SqlCommand4 As New SqlClient.SqlCommand

SqlCommand4.CommandText = cmd

SqlAdapter4.SelectCommand = SqlCommand4

SqlAdapter4.SelectCommand.Connection = SqlConnection4

SqlAdapter4.SelectCommand.Connection.Open()

SqlAdapter4.Fill(DataSet4)

Dim nfio As String = DataSet4.Tables.Item(0).Rows.Count.ToString

SqlAdapter4.SelectCommand.Connection.Close()

oPara4.Range.ParagraphFormat.SpaceBefore = 0

oPara4.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara4.Range.ParagraphFormat.SpaceAfter = 0

oPara4.Range.ParagraphFormat.SpaceAfterAuto = False

oPara4.Range.Text = "Всего пользователей - " & vbTab & nfio

oPara4.Range.Font.Size = 11

oPara4.Range.Font.Bold = True

oPara4.Range.InsertParagraphAfter()

Catch ex As Exception

End Try

End Sub

Private Sub Otchet_Vyp()

Dim StartDate As String = Form6.GetDateString(DateTimePicker1.Value)

Dim EndDate As String = Form6.GetDateString(DateTimePicker2.Value)

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter1 As New SqlClient.SqlDataAdapter

Dim DataSet1 As New DataSet

Dim SqlConnection1 As New SqlClient.SqlConnection

SqlConnection1.ConnectionString = con_string

Dim SqlCommand1 As New SqlClient.SqlCommand

SqlCommand1.CommandText = "select [id_ticket],[fio],[id_incident] from [ticket]" & vbCrLf & _

"where [id_status] = 5 and [otklon] = 0 " & vbCrLf & _

"and [open_ticket] >= convert(datetime,'" & StartDate & "',12) and [open_ticket] <= convert(datetime,'" & EndDate & "',12)"

SqlAdapter1.SelectCommand = SqlCommand1

SqlAdapter1.SelectCommand.Connection = SqlConnection1

Dim c_row, c_col As Integer

Dim id_ticket, fio, incident, comment As String

Try

Dim oWord As Word.Application

Dim oDoc As Word.Document

Dim oTable As Word.Table

Dim oPara0, oPara1, oPara2, oPara3, oPara4 As Word.Paragraph

oWord = CreateObject("Word.Application")

oWord.Visible = True

oDoc = oWord.Documents.Add

oPara0 = oDoc.Content.Paragraphs.Add

oPara0.Range.Text = "Отчет о выполненных заявках - " & DateAndTime.Now.ToString

oPara0.Range.Font.Size = 13

oPara0.Range.Font.Bold = True

oPara0.Range.ParagraphFormat.SpaceBefore = 0

oPara0.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara0.Range.ParagraphFormat.SpaceAfter = 0

oPara0.Range.ParagraphFormat.SpaceAfterAuto = False

oPara0.Range.InsertParagraphAfter()

oPara1 = oDoc.Content.Paragraphs.Add

oPara1.Range.Text = "За период - с " & DateTimePicker1.Value.ToString & _

" по " & DateTimePicker2.Value.ToString

oPara1.Range.Font.Size = 12

oPara1.Range.Font.Bold = False

oPara1.Range.ParagraphFormat.SpaceBefore = 0

oPara1.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara1.Range.ParagraphFormat.SpaceAfter = 20

oPara1.Range.ParagraphFormat.SpaceAfterAuto = False

oPara1.Range.InsertParagraphAfter()

SqlAdapter1.SelectCommand.Connection.Open()

SqlAdapter1.Fill(DataSet1)

c_row = DataSet1.Tables.Item(0).Rows.Count

c_col = DataSet1.Tables.Item(0).Columns.Count

oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, c_row + 1, 3)

oTable.Range.Font.Size = 11

oTable.Range.Font.Bold = False

oTable.Rows.Borders.Enable = True

oTable.Columns.Borders.Enable = True

oTable.Columns(1).SetWidth(43, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(2).SetWidth(80, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(3).SetWidth(349, Word.WdRulerStyle.wdAdjustNone)

oTable.Rows.Item(1).Range.Font.Bold = True

oTable.Rows.Item(1).Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15

oTable.Cell(1, 1).Range.Text = "Номер"

oTable.Cell(1, 2).Range.Text = "Пользователь"

oTable.Cell(1, 3).Range.Text = "Инцидент"

For i = 1 To c_row

id_ticket = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(0)

fio = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(1)

incident = GetIncident(DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(2))

comment = GetComment(id_ticket)

oTable.Cell(i + 1, 1).Range.Text = CStr(id_ticket)

oTable.Cell(i + 1, 2).Range.Text = CStr(fio)

oTable.Cell(i + 1, 3).Range.Text = CStr(incident)

Next

SqlAdapter1.SelectCommand.Connection.Close()

oTable.Range.ParagraphFormat.SpaceBefore = 0

oTable.Range.ParagraphFormat.SpaceBeforeAuto = False

oTable.Range.ParagraphFormat.SpaceAfter = 0

oTable.Range.ParagraphFormat.SpaceAfterAuto = False

oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

oPara2.Range.ParagraphFormat.SpaceBefore = 0

oPara2.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara2.Range.ParagraphFormat.SpaceAfter = 0

oPara2.Range.ParagraphFormat.SpaceAfterAuto = False

oPara2.Range.Text = "Всего заявок - " & vbTab & vbTab & vbTab & c_row.ToString

oPara2.Range.Font.Size = 11

oPara2.Range.Font.Bold = True

oPara2.Range.InsertParagraphAfter()

oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter3 As New SqlClient.SqlDataAdapter

Dim DataSet3 As New DataSet

Dim SqlConnection3 As New SqlClient.SqlConnection

Dim cmd As String = "select distinct [id_incident] from [ticket] where [id_status] = 5 and [otklon] = 0 " & _

"and [open_ticket] >= convert(datetime,'" & StartDate & "',12) and [open_ticket] <= convert(datetime,'" & EndDate & "',12)"

SqlConnection3.ConnectionString = con_string

Dim SqlCommand3 As New SqlClient.SqlCommand

SqlCommand3.CommandText = cmd

SqlAdapter3.SelectCommand = SqlCommand3

SqlAdapter3.SelectCommand.Connection = SqlConnection3

SqlAdapter3.SelectCommand.Connection.Open()

SqlAdapter3.Fill(DataSet3)

Dim ninc As String = DataSet3.Tables.Item(0).Rows.Count.ToString

SqlAdapter3.SelectCommand.Connection.Close()

oPara3.Range.ParagraphFormat.SpaceBefore = 0

oPara3.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara3.Range.ParagraphFormat.SpaceAfter = 0

oPara3.Range.ParagraphFormat.SpaceAfterAuto = False

oPara3.Range.Text = "Всего типов инцидентов - " & vbTab & ninc

oPara3.Range.Font.Size = 11

oPara3.Range.Font.Bold = True

oPara3.Range.InsertParagraphAfter()

oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter4 As New SqlClient.SqlDataAdapter

Dim DataSet4 As New DataSet

Dim SqlConnection4 As New SqlClient.SqlConnection

cmd = "select distinct convert(char(11),[fio]) from [ticket] where [id_status] = 5 and [otklon] = 0 " & _

"and [open_ticket] >= convert(datetime,'" & StartDate & "',12) and [open_ticket] <= convert(datetime,'" & EndDate & "',12)"

SqlConnection4.ConnectionString = con_string

Dim SqlCommand4 As New SqlClient.SqlCommand

SqlCommand4.CommandText = cmd

SqlAdapter4.SelectCommand = SqlCommand4

SqlAdapter4.SelectCommand.Connection = SqlConnection4

SqlAdapter4.SelectCommand.Connection.Open()

SqlAdapter4.Fill(DataSet4)

Dim nfio As String = DataSet4.Tables.Item(0).Rows.Count.ToString

SqlAdapter4.SelectCommand.Connection.Close()

oPara4.Range.ParagraphFormat.SpaceBefore = 0

oPara4.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara4.Range.ParagraphFormat.SpaceAfter = 0

oPara4.Range.ParagraphFormat.SpaceAfterAuto = False

oPara4.Range.Text = "Всего пользователей - " & vbTab & nfio

oPara4.Range.Font.Size = 11

oPara4.Range.Font.Bold = True

oPara4.Range.InsertParagraphAfter()

Catch ex As Exception

End Try

End Sub

Private Sub Otchet_PoIsp()

Dim StartDate As String = Form6.GetDateString(DateTimePicker1.Value)

Dim EndDate As String = Form6.GetDateString(DateTimePicker2.Value)

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter1 As New SqlClient.SqlDataAdapter

Dim DataSet1 As New DataSet

Dim SqlConnection1 As New SqlClient.SqlConnection

SqlConnection1.ConnectionString = con_string

Dim SqlCommand1 As New SqlClient.SqlCommand

SqlCommand1.CommandText = "select distinct [id_users] from [ticket]" & vbCrLf & _

"where [otklon] = 0 " & vbCrLf & _

"and [open_ticket] >= convert(datetime,'" & _

StartDate & "',12) and [open_ticket] <= convert(datetime,'" & _

EndDate & "',12)"

SqlAdapter1.SelectCommand = SqlCommand1

SqlAdapter1.SelectCommand.Connection = SqlConnection1

Dim c_row, c1_row As Integer

Dim id_ticket, fio, incident As String

Try

Dim oWord As Word.Application

Dim oDoc As Word.Document

Dim oPara0, oPara1 As Word.Paragraph

oWord = CreateObject("Word.Application")

oWord.Visible = True

oDoc = oWord.Documents.Add

oPara0 = oDoc.Content.Paragraphs.Add

oPara0.Range.Text = "Отчет о заявках по исполнителям - " & DateAndTime.Now.ToString

oPara0.Range.Font.Size = 13

oPara0.Range.Font.Bold = True

oPara0.Range.ParagraphFormat.SpaceBefore = 0

oPara0.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara0.Range.ParagraphFormat.SpaceAfter = 0

oPara0.Range.ParagraphFormat.SpaceAfterAuto = False

oPara0.Range.InsertParagraphAfter()

oPara1 = oDoc.Content.Paragraphs.Add

oPara1.Range.Text = "За период - с " & DateTimePicker1.Value.ToString & _

" по " & DateTimePicker2.Value.ToString

oPara1.Range.Font.Size = 12

oPara1.Range.Font.Bold = False

oPara1.Range.ParagraphFormat.SpaceBefore = 0

oPara1.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara1.Range.ParagraphFormat.SpaceAfter = 20

oPara1.Range.ParagraphFormat.SpaceAfterAuto = False

oPara1.Range.InsertParagraphAfter()

SqlAdapter1.SelectCommand.Connection.Open()

SqlAdapter1.Fill(DataSet1)

c_row = DataSet1.Tables.Item(0).Rows.Count

Dim id_user As Integer

Dim n_user As String

For i = 1 To c_row

id_user = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(0)

n_user = GetAdmin(id_user)

oPara1.Range.Font.Bold = True

oPara1.Range.ParagraphFormat.SpaceAfter = 10

oPara1.Range.InsertAfter(n_user & vbCrLf)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "select [id_ticket],[fio],[id_incident] from [ticket] where [otklon] = 0 " & _

"and [open_ticket] >= convert(datetime,'" & _

StartDate & "',12) and [open_ticket] <= convert(datetime,'" & _

EndDate & "',12) and [id_users] = " & id_user.ToString

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

c1_row = DataSet2.Tables.Item(0).Rows.Count

For j = 1 To c1_row

oPara1.Range.Font.Bold = False

oPara1.Range.ParagraphFormat.SpaceAfter = 0

id_ticket = DataSet2.Tables.Item(0).Rows.Item(j - 1).Item(0)

fio = DataSet2.Tables.Item(0).Rows.Item(j - 1).Item(1)

incident = GetIncident(DataSet2.Tables.Item(0).Rows.Item(j - 1).Item(2))

oPara1.Range.InsertAfter(id_ticket & vbTab & fio & vbTab & incident & vbCrLf)

Next

Next

Catch ex As Exception

End Try

End Sub

Private Sub Otchet_Otkl()

Dim StartDate As String = Form6.GetDateString(DateTimePicker1.Value)

Dim EndDate As String = Form6.GetDateString(DateTimePicker2.Value)

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter1 As New SqlClient.SqlDataAdapter

Dim DataSet1 As New DataSet

Dim SqlConnection1 As New SqlClient.SqlConnection

SqlConnection1.ConnectionString = con_string

Dim SqlCommand1 As New SqlClient.SqlCommand

SqlCommand1.CommandText = "select [id_ticket],[fio],[id_incident] from [ticket]" & vbCrLf & _

"where [otklon] = 1 " & vbCrLf & _

"and [open_ticket] >= convert(datetime,'" & _

StartDate & "',12) and [open_ticket] <= convert(datetime,'" & _

EndDate & "',12)"

SqlAdapter1.SelectCommand = SqlCommand1

SqlAdapter1.SelectCommand.Connection = SqlConnection1

Dim c_row, c_col As Integer

Dim id_ticket, fio, incident, comment As String

Try

Dim oWord As Word.Application

Dim oDoc As Word.Document

Dim oTable As Word.Table

Dim oPara0, oPara1, oPara2, oPara3, oPara4 As Word.Paragraph

oWord = CreateObject("Word.Application")

oWord.Visible = True

oDoc = oWord.Documents.Add

oPara0 = oDoc.Content.Paragraphs.Add

oPara0.Range.Text = "Отчет об отклоненных заявках - " & DateAndTime.Now.ToString

oPara0.Range.Font.Size = 13

oPara0.Range.Font.Bold = True

oPara0.Range.ParagraphFormat.SpaceBefore = 0

oPara0.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara0.Range.ParagraphFormat.SpaceAfter = 0

oPara0.Range.ParagraphFormat.SpaceAfterAuto = False

oPara0.Range.InsertParagraphAfter()

oPara1 = oDoc.Content.Paragraphs.Add

oPara1.Range.Text = "За период - с " & DateTimePicker1.Value.ToString & _

" по " & DateTimePicker2.Value.ToString

oPara1.Range.Font.Size = 12

oPara1.Range.Font.Bold = False

oPara1.Range.ParagraphFormat.SpaceBefore = 0

oPara1.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara1.Range.ParagraphFormat.SpaceAfter = 20

oPara1.Range.ParagraphFormat.SpaceAfterAuto = False

oPara1.Range.InsertParagraphAfter()

SqlAdapter1.SelectCommand.Connection.Open()

SqlAdapter1.Fill(DataSet1)

c_row = DataSet1.Tables.Item(0).Rows.Count

c_col = DataSet1.Tables.Item(0).Columns.Count

oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, c_row + 1, 4)

oTable.Range.ParagraphFormat.SpaceAfter = 6

oTable.Range.Font.Size = 11

oTable.Range.Font.Bold = False

oTable.Rows.Borders.Enable = True

oTable.Columns.Borders.Enable = True

oTable.Columns(1).SetWidth(43, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(2).SetWidth(80, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(3).SetWidth(170, Word.WdRulerStyle.wdAdjustNone)

oTable.Columns(4).SetWidth(179, Word.WdRulerStyle.wdAdjustNone)

oTable.Rows.Item(1).Range.Font.Bold = True

oTable.Rows.Item(1).Shading.BackgroundPatternColor = Word.WdColor.wdColorGray15

oTable.Cell(1, 1).Range.Text = "Номер"

oTable.Cell(1, 2).Range.Text = "Пользователь"

oTable.Cell(1, 3).Range.Text = "Инцидент"

oTable.Cell(1, 4).Range.Text = "Текст заявки"

For i = 1 To c_row

id_ticket = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(0)

fio = DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(1)

incident = GetIncident(DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(2))

comment = GetComment(id_ticket)

oTable.Cell(i + 1, 1).Range.Text = CStr(id_ticket)

oTable.Cell(i + 1, 2).Range.Text = CStr(fio)

oTable.Cell(i + 1, 3).Range.Text = CStr(incident)

oTable.Cell(i + 1, 4).Range.Text = Replace(CStr(comment), "----Пользователь----" & vbCrLf, "")

Next

oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

oPara2.Range.ParagraphFormat.SpaceBefore = 0

oPara2.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara2.Range.ParagraphFormat.SpaceAfter = 0

oPara2.Range.ParagraphFormat.SpaceAfterAuto = False

oPara2.Range.Text = "Всего заявок - " & vbTab & vbTab & vbTab & c_row

oPara2.Range.Font.Size = 11

oPara2.Range.Font.Bold = True

oPara2.Range.InsertParagraphAfter()

oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter3 As New SqlClient.SqlDataAdapter

Dim DataSet3 As New DataSet

Dim SqlConnection3 As New SqlClient.SqlConnection

Dim cmd As String = "select distinct [id_incident] from [ticket] where [otklon] = 1 " & _

"and [open_ticket] >= convert(datetime,'" & _

StartDate & "',12) and [open_ticket] <= convert(datetime,'" & _

EndDate & "',12)"

SqlConnection3.ConnectionString = con_string

Dim SqlCommand3 As New SqlClient.SqlCommand

SqlCommand3.CommandText = cmd

SqlAdapter3.SelectCommand = SqlCommand3

SqlAdapter3.SelectCommand.Connection = SqlConnection3

SqlAdapter3.SelectCommand.Connection.Open()

SqlAdapter3.Fill(DataSet3)

Dim ninc As String = DataSet3.Tables.Item(0).Rows.Count.ToString

SqlAdapter3.SelectCommand.Connection.Close()

oPara3.Range.ParagraphFormat.SpaceBefore = 0

oPara3.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara3.Range.ParagraphFormat.SpaceAfter = 0

oPara3.Range.ParagraphFormat.SpaceAfterAuto = False

oPara3.Range.Text = "Всего типов инцидентов - " & vbTab & ninc

oPara3.Range.Font.Size = 11

oPara3.Range.Font.Bold = True

oPara3.Range.InsertParagraphAfter()

oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)

Dim SqlAdapter4 As New SqlClient.SqlDataAdapter

Dim DataSet4 As New DataSet

Dim SqlConnection4 As New SqlClient.SqlConnection

cmd = "select distinct convert(char(11),[fio]) from [ticket] where [otklon] = 1 " & _

"and [open_ticket] >= convert(datetime,'" & _

StartDate & "',12) and [open_ticket] <= convert(datetime,'" & _

EndDate & "',12)"

SqlConnection4.ConnectionString = con_string

Dim SqlCommand4 As New SqlClient.SqlCommand

SqlCommand4.CommandText = cmd

SqlAdapter4.SelectCommand = SqlCommand4

SqlAdapter4.SelectCommand.Connection = SqlConnection4

SqlAdapter4.SelectCommand.Connection.Open()

SqlAdapter4.Fill(DataSet4)

Dim nfio As String = DataSet4.Tables.Item(0).Rows.Count.ToString

SqlAdapter4.SelectCommand.Connection.Close()

oPara4.Range.ParagraphFormat.SpaceBefore = 0

oPara4.Range.ParagraphFormat.SpaceBeforeAuto = False

oPara4.Range.ParagraphFormat.SpaceAfter = 0

oPara4.Range.ParagraphFormat.SpaceAfterAuto = False

oPara4.Range.Text = "Всего пользователей - " & vbTab & nfio

oPara4.Range.Font.Size = 11

oPara4.Range.Font.Bold = True

oPara4.Range.InsertParagraphAfter()

Catch ex As Exception

End Try

End Sub

Private Sub Otchet_ProblUsers()

End Sub

Private Sub Otchet_ProblPC()

End Sub

Private Sub Otchet_Dynamic()

End Sub

Private Function GetIncident(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "SELECT [name_incident]" & _

"FROM [incident] where id_incident = " & CStr(id)

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

GetIncident = DataSet2.Tables.Item(0).Rows.Item(0).Item(0)

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Private Function GetComment(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "SELECT [comments]" & _

"FROM [comments] where id_ticket = " & CStr(id)

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

GetComment = DataSet2.Tables.Item(0).Rows.Item(0).Item(0)

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Private Function GetAdmin(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & Form1.srv & ";Initial Catalog=" & Form1.it_db & ";Integrated Security=True"

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim cmd As String = "SELECT [admin]" & _

"FROM [admins] where id_users = " & CStr(id)

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

GetAdmin = DataSet2.Tables.Item(0).Rows.Item(0).Item(0)

SqlAdapter2.SelectCommand.Connection.Close()

End Function

End Class

Imports System.Data.Odbc



Public Class Form5

Private Sub Form5_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form1.Visible = False

End Sub

Private Sub Form5_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed

Form1.Visible = True

Form1.Activate()

End Sub

Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Label3.Text = "Действие выполнять" & vbCrLf & "один раз"

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Form1.srv = TextBox1.Text

Form1.it_db = TextBox2.Text

End Sub

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

DBCreate()

End Sub



Private Sub DBCreate()

Dim cmd1 As String = _

"CREATE DATABASE [" & TextBox2.Text & "] ON PRIMARY " & vbCrLf & _

"( NAME = N'" & TextBox2.Text & "', FILENAME = N'C:\" & TextBox2.Text & ".mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )" & vbCrLf & _

" LOG ON " & vbCrLf & _

"( NAME = N'" & TextBox2.Text & "_log', FILENAME = N'C:\" & TextBox2.Text & "_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)" &

vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

COMPATIBILITY_LEVEL = 100" & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET ANSI_NULL_DEFAULT OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET ANSI_NULLS OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET ANSI_PADDING

OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET ANSI_WARNINGS

OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET ARITHABORT OFF

" & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET AUTO_CLOSE OFF

" & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

AUTO_CREATE_STATISTICS ON " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET AUTO_SHRINK

OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

AUTO_UPDATE_STATISTICS ON " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

CURSOR_CLOSE_ON_COMMIT OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

CURSOR_DEFAULT GLOBAL " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

CONCAT_NULL_YIELDS_NULL OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

NUMERIC_ROUNDABORT OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

QUOTED_IDENTIFIER OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

RECURSIVE_TRIGGERS OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET DISABLE_BROKER

" & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

AUTO_UPDATE_STATISTICS_ASYNC OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

DATE_CORRELATION_OPTIMIZATION OFF " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET

PARAMETERIZATION SIMPLE " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET READ_WRITE " &

vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET RECOVERY

SIMPLE " & vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET MULTI_USER " &

vbCrLf & _

"ALTER DATABASE [" & TextBox2.Text & "] SET PAGE_VERIFY

CHECKSUM "

Dim cmd2 As String = _

"USE [" & TextBox2.Text & "]" & vbCrLf & _

"IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [" &

TextBox2.Text & "] MODIFY FILEGROUP [PRIMARY] DEFAULT" &

vbCrLf & _

"USE [" & TextBox2.Text & "]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[status](" & vbCrLf & _

"[id_status] [int] IDENTITY(1,1) NOT NULL," & vbCrLf & _

"[name_status] [text] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_status] PRIMARY KEY CLUSTERED " & vbCrLf & _

"(" & vbCrLf & _

"[id_status] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[priority](" & vbCrLf & _

"[id_priority] [int] IDENTITY(1,1) NOT NULL," & vbCrLf & _

"[name_priority] [text] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_priority] PRIMARY KEY CLUSTERED " & vbCrLf & _

"(" & vbCrLf & _

"[id_priority] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[incident](" & vbCrLf & _

"[id_incident] [int] IDENTITY(1,1) NOT NULL," & vbCrLf & _

"[name_incident] [text] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_incident] PRIMARY KEY CLUSTERED " & vbCrLf & _

"(" & vbCrLf & _

"[id_incident] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[admins](" & vbCrLf & _

"[id_users] [int] IDENTITY(-1,1) NOT NULL," & vbCrLf & _

"[admin] [text] NOT NULL," & vbCrLf & _

"[login] [text] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_admins] PRIMARY KEY CLUSTERED " & vbCrLf & _

"(" & vbCrLf & _

"[id_users] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[ticket](" & vbCrLf & _

"[id_ticket] [int] IDENTITY(1,1) NOT NULL," & vbCrLf & _

"[fio] [text] NOT NULL," & vbCrLf & _

"[phone] [text] NOT NULL," & vbCrLf & _

"[id_incident] [int] NOT NULL," & vbCrLf & _

"[id_priority] [int] NOT NULL," & vbCrLf & _

"[id_status] [int] NOT NULL," & vbCrLf & _

"[id_users] [int] NULL," & vbCrLf & _

"[open_ticket] [datetime] NOT NULL," & vbCrLf & _

"[close_ticket] [datetime] NULL," & vbCrLf & _

"[work_time] [time] NULL," & vbCrLf & _

"[tabel] [smallint] NOT NULL," & vbCrLf & _

"[pc_inv] [text] NOT NULL," & vbCrLf & _

"[room] [text] NOT NULL," & vbCrLf & _

"[deleted] [bit] NOT NULL," & vbCrLf & _

"[otklon] [bit] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_ticket] PRIMARY KEY CLUSTERED " & vbCrLf

& _

"(" & vbCrLf & _

"[id_ticket] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"CREATE TABLE [dbo].[comments](" & vbCrLf & _

"[id_ticket] [int] NOT NULL," & vbCrLf & _

"[dt_comments] [datetime] NOT NULL," & vbCrLf & _

"[comments] [text] NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_comments] PRIMARY KEY CLUSTERED " & vbCrLf & _

"(" & vbCrLf & _

"[id_ticket] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" & vbCrLf & _

"SET ANSI_NULLS ON" & vbCrLf & _

"SET QUOTED_IDENTIFIER ON" & vbCrLf & _

"SET ANSI_PADDING ON" & vbCrLf & _

"CREATE TABLE [dbo].[attach](" & vbCrLf & _

"[id_ticket] [int] NOT NULL," & vbCrLf & _

"[image] [varbinary](max) NOT NULL," & vbCrLf & _

" CONSTRAINT [PK_attach] PRIMARY KEY CLUSTERED " & vbCrLf

& _

"(" & vbCrLf & _

"[id_ticket] ASC" & vbCrLf & _

")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf & _

") ON [PRIMARY]" & vbCrLf & _

"SET ANSI_PADDING OFF" & vbCrLf & _

"ALTER TABLE [dbo].[attach] WITH CHECK ADD CONSTRAINT [FK_attach_ticket] FOREIGN KEY([id_ticket])" & vbCrLf & _

"REFERENCES [dbo].[ticket] ([id_ticket])" & vbCrLf & _

"ON UPDATE CASCADE" & vbCrLf & _

"ALTER TABLE [dbo].[attach] CHECK CONSTRAINT [FK_attach_ticket]" & vbCrLf & _

"ALTER TABLE [dbo].[comments] WITH CHECK ADD CONSTRAINT [FK_comments_ticket] FOREIGN KEY([id_ticket])" & vbCrLf & _

"REFERENCES [dbo].[ticket] ([id_ticket])" & vbCrLf & _

"ON UPDATE CASCADE" & vbCrLf & _

"ALTER TABLE [dbo].[comments] CHECK CONSTRAINT [FK_comments_ticket]" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] WITH CHECK ADD CONSTRAINT [FK_ticket_admins] FOREIGN KEY([id_users])" & vbCrLf & _

"REFERENCES [dbo].[admins] ([id_users])" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_admins]" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] WITH CHECK ADD CONSTRAINT [FK_ticket_incident] FOREIGN KEY([id_incident])" & vbCrLf & _

"REFERENCES [dbo].[incident] ([id_incident])" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_incident]" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] WITH CHECK ADD CONSTRAINT [FK_ticket_priority] FOREIGN KEY([id_priority])" & vbCrLf & _

"REFERENCES [dbo].[priority] ([id_priority])" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_priority]" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] WITH CHECK ADD CONSTRAINT [FK_ticket_status] FOREIGN KEY([id_status])" & vbCrLf & _

"REFERENCES [dbo].[status] ([id_status])" & vbCrLf & _

"ALTER TABLE [dbo].[ticket] CHECK CONSTRAINT [FK_ticket_status]"

Dim cmd3 As String = _

"USE [" & TextBox2.Text & "]" & vbCrLf & _

"INSERT" & vbCrLf & _

"INTO admins(admin,login)" & vbCrLf & _

"VALUES ('Не назначен','Нет')" & vbCrLf & _

"INSERT" & vbCrLf & _

"INTO incident(name_incident)" & vbCrLf & _

"VALUES ('Другое'),('Не открывается файл'),('Отсутствуют сетевые диски'),('Перемещение техники')," & vbCrLf & _

"('Проблема с интернетом'),('Проблема с компьютером'),('Проблема с сетью'),('Проблема с электронной почтой')," & vbCrLf & _

"('Проблема с принтером'),('Создание нового рабочего места'),('Телефония')" & vbCrLf & _

"INSERT" & vbCrLf & _

"INTO priority(name_priority)" & vbCrLf & _

"VALUES ('Низкий'),('Нормальный'),('Высокий')" & vbCrLf & _

"INSERT" & vbCrLf & _

"INTO status(name_status)" & vbCrLf & _

"VALUES ('Открыта'),('Назначена'),('В работе'),('Отложена'),('Закрыта')"

Dim conn_string As String = "Driver={SQL Server};Server=" & TextBox1.Text & ";Trusted_Connection=Yes;Database=master;"

If Not Form1.TestConnect(TextBox1.Text, TextBox2.Text) Then

ExQuery(conn_string, cmd1)

conn_string = "Driver={SQL Server};Server=" & TextBox1.Text & ";Trusted_Connection=Yes;Database=" & TextBox2.Text & ";"

ExQuery(conn_string, cmd2)

ExQuery(conn_string, cmd3)

MsgBox("База " & Chr(34) & TextBox2.Text & Chr(34) & " создана на сервере " & _

Chr(34) & TextBox1.Text & Chr(34) & ".", _

MsgBoxStyle.Information, "Сообщение")

Else

MsgBox("База " & Chr(34) & TextBox2.Text & Chr(34) & " не создана на сервере " & _

Chr(34) & TextBox1.Text & Chr(34) & ", т.к. существует база с таким же именем.", _

MsgBoxStyle.Information, "Сообщение")

End If

End Sub

Function ExQuery(ByVal connectionString As String, ByVal cmd As String) As Integer

Dim command As New OdbcCommand(cmd)

Using connection As New OdbcConnection(connectionString)

command.Connection = connection

Try

connection.Open()

ExQuery = command.ExecuteNonQuery()

Catch ex As Exception

MsgBox("Ошибка: " & ex.Message, MsgBoxStyle.Information, "Сообщение")

End Try

End Using

End Function

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

Form6.Visible = True

Form6.Activate()

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Form7.Visible = True

Form7.Activate()

End Sub

End Class

Public Class Form6

Private Sub Form6_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form5.Visible = False

End Sub

Private Sub Form6_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed

Form5.Visible = True

Form5.Activate()

End Sub

Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged

If CheckBox1.Checked Then

DateTimePicker1.Enabled = True

Else

DateTimePicker1.Enabled = False

End If

End Sub

Private Sub CheckBox2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox2.CheckedChanged

If CheckBox2.Checked Then

DateTimePicker2.Enabled = True

Else

DateTimePicker2.Enabled = False

End If

End Sub

Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DateTimePicker1.MaxDate = Date.Today

DateTimePicker2.MaxDate = Date.Today

End Sub

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

Dim StartDate, EndDate As String

If CheckBox1.Checked Then

StartDate = GetDateString(DateTimePicker1.Value.Date)

Else

StartDate = "20120101 00:00:00"

End If

If CheckBox2.Checked Then

EndDate = GetDateString(DateTimePicker2.Value.Date)

Else

EndDate = "21001231 00:00:00"

End If

If Not DateTimePicker1.Value.Date = DateTimePicker2.Value.Date Then

Dim cmd As String = ""

Dim cmd1 As String = _

"declare @mint int, @maxt int" & vbCrLf & _

"SELECT @mint=MIN(id_ticket)" & vbCrLf & _

"FROM [ticket] where [open_ticket] > convert(datetime,'" & StartDate & "',12) and [open_ticket] < convert(datetime,'" & EndDate & "',12)" & vbCrLf & _

"select @maxt=MAX(id_ticket)" & vbCrLf & _

"FROM [ticket] where [open_ticket] > convert(datetime,'" & StartDate & "',12) and [open_ticket] < convert(datetime,'" & EndDate & "',12)" & vbCrLf

Dim cmd2 As String = _

"UPDATE ticket" & vbCrLf & _

"SET deleted = 1 where ([id_ticket] >= @mint) and ([id_ticket] <= @maxt) and ([deleted] = 0)"

Dim cmd3 As String = _

"delete from [comments] where ([id_ticket] >= @mint) and ([id_ticket] <= @maxt)" & vbCrLf & _

"delete from [attach] where ([id_ticket] >= @mint) and ([id_ticket] <= @maxt)" & vbCrLf & _

"delete from [ticket] where ([id_ticket] >= @mint) and ([id_ticket] <= @maxt)"

Dim conn_string As String = "Driver={SQL Server};Server=" & Form1.srv & ";Trusted_Connection=Yes;Database=" & Form1.it_db & ";"

If CheckBox3.Checked Then

cmd = cmd1 & cmd3

Else

cmd = cmd1 & cmd2

End If

Dim delt As Integer = Form5.ExQuery(conn_string, cmd)

If delt > 0 Then

If CheckBox3.Checked Then

MsgBox("Удалено записей - " & CStr(delt / 3), MsgBoxStyle.Information, "Сообщение")

Else

MsgBox("Удалено записей - " & CStr(delt), MsgBoxStyle.Information, "Сообщение")

End If

Else

MsgBox("Нет данных для удаления", MsgBoxStyle.Information, "Сообщение")

End If

Else

MsgBox("Нулевой интервал. Измените дату", MsgBoxStyle.Information, "Сообщение")

End If

End Sub

Function GetDateString(ByVal dt As DateTime) As String

Dim sd, sm, sy, sh, sn, ss As String

Dim dn As Date = dt

If DateAndTime.Hour(dn) < 10 Then

sh = "0" + DateAndTime.Hour(dn).ToString

Else

sh = DateAndTime.Hour(dn).ToString

End If

If DateAndTime.Minute(dn) < 10 Then

sn = "0" + DateAndTime.Minute(dn).ToString

Else

sn = DateAndTime.Minute(dn).ToString

End If

If DateAndTime.Second(dn) < 10 Then

ss = "0" + DateAndTime.Second(dn).ToString

Else

ss = DateAndTime.Second(dn).ToString

End If

sy = DateAndTime.Year(dn).ToString

If DateAndTime.Month(dn) < 10 Then

sm = "0" + DateAndTime.Month(dn).ToString

Else

sm = DateAndTime.Month(dn).ToString

End If

If DateAndTime.Day(dn) < 10 Then

sd = "0" + DateAndTime.Day(dn).ToString

Else

sd = DateAndTime.Day(dn).ToString

End If

GetDateString = sy & sm & sd & " " & sh & ":" & sn & ":" & ss

End Function

End Class

Public Class Form7

Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

CheckBox3.Text = "Физическое удаление" & vbCrLf & "записей"

End Sub

Private Sub Form7_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed

Form5.Visible = True

Form5.Activate()

End Sub



Private Sub Form7_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form5.Visible = False

End Sub

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

Dim cmd As String = ""

Dim cmd1 As String = _

"UPDATE ticket" & vbCrLf & _

"SET deleted = 1 where ([deleted] = 0)"

Dim cmd2 As String = _

"delete from [comments]" & vbCrLf & _

"delete from [attach]" & vbCrLf & _

"delete from [ticket]"

Dim conn_string As String = "Driver={SQL Server};Server=" & Form1.srv & ";Trusted_Connection=Yes;Database=" & Form1.it_db & ";"

If CheckBox3.Checked Then

cmd = cmd2

Else

cmd = cmd1

End If

Dim delt As Integer = Form5.ExQuery(conn_string, cmd)

If delt > 0 Then

If CheckBox3.Checked Then

MsgBox("Удалено записей - " & CStr(delt / 3), MsgBoxStyle.Information, "Сообщение")

Else

MsgBox("Удалено записей - " & CStr(delt), MsgBoxStyle.Information, "Сообщение")

End If

Me.Close()

Else

MsgBox("Нет данных для удаления", MsgBoxStyle.Information, "Сообщение")

End If

End Sub

End Class

Размещено на Allbest.ru