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 GetStatus(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" &

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_status]" & _

"FROM [status] where id_status = " & 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)

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

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Private Function GetUsersID(ByVal admin As String) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" &

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 [id_users]" & _

"FROM [admins] where [admin] like '" & CStr(admin) & "'"

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)

GetUsersID = CStr(DataSet2.Tables.Item(0).Rows.Item(0).Item(0))

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Sub FillDataGridView(ByVal server As String, ByVal DBN As String,

ByRef DGV As DataGridView, ByVal status As String)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim con_string As String = "Data Source=" & server & ";Initial Catalog="

& DBN & ";Integrated Security=True"

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim c_row, c_col As Integer

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

",[fio]" & _

",[phone]" & _

",[pc_inv]" & _

",[room]" & _

",[open_ticket]" & _

",[id_incident]" & _

"FROM [ticket] WHERE [deleted] = 0 and [otklon] = 0 and [id_status] = "

& status

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Close()

Try

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

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

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

If c_row > 0 Then

DGV.RowCount = c_row

DGV.ColumnCount = c_col

DGV.Columns.Item(0).HeaderText = "Номер заявки"

DGV.Columns.Item(1).HeaderText = "ФИО"

DGV.Columns.Item(2).HeaderText = "Телефон"

DGV.Columns.Item(3).HeaderText = "Имя компьютера"

DGV.Columns.Item(4).HeaderText = "Кабинет"

DGV.Columns.Item(5).HeaderText = "Открыта"

DGV.Columns.Item(6).HeaderText = "Тип инцидента"

For i = 1 To c_row

For j = 1 To c_col

If j = 7 Then

DGV.Item(j - 1, i - 1).Value =

GetIncident(DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1))

Else

If j = 0 Then DGV.Item(j - 1, i - 1).Value =

DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1)

DGV.Item(j - 1, i - 1).Value = DataSet2.Tables.Item(0).Rows.Item(i -

1).Item(j - 1).ToString

End If

Next

Next

End If

Catch ex As Exception

End Try

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Timer1.Tick

Dim m1, m2 As String

Dim rc1, rc2 As Integer

FillForm()

rc1 = DataGridView1.RowCount

rc2 = DataGridView2.RowCount

If c1 < rc1 Or c2 < rc2 Then

If c1 < rc1 Then

m1 = vbTab & "Поступившие - " & CStr(rc1 - c1) & "(" & rc1 & ")" &

vbCrLf

c1 = rc1

Else

m1 = ""

End If

If c2 < rc2 Then

m2 = vbTab & "Закрытые - " & CStr(rc2 - c2) & "(" & rc2 & ")"

c2 = rc2

Else

m2 = ""

End If

If Me.WindowState = FormWindowState.Minimized Then

Me.Show()

Me.WindowState = FormWindowState.Normal

End If

NotifyIcon1.ShowBalloonTip(5000, "ticket_operator", "ЗАЯВКИ:" &

vbTab & vbTab & vbCrLf & _

m1 & m2, ToolTipIcon.Info)

End If

End Sub

Private Sub DataGridView1_CellDoubleClick(ByVal sender As

System.Object, ByVal e As

System.Windows.Forms.DataGridViewCellEventArgs) Handles

DataGridView1.CellDoubleClick, DataGridView2.CellDoubleClick

Dim cur_row As Integer = sender.CurrentRow.Index

Dim cur_id_value As Integer = sender.Item(0, cur_row).Value

Dim cur_inc_value As String = sender.Item(6, cur_row).Value

Label1.Text = CStr(cur_id_value)

Form3.L_user.Text = "Пользователь - " & sender.Item(1, cur_row).Value

& _

" Тел. " & sender.Item(2, cur_row).Value & _

" Каб. " & sender.Item(4, cur_row).Value

OpenTicket(cur_id_value, cur_inc_value)

tmp1 = img_path & "\image_z_" & CStr(cur_id_value) & ".jpg"

SqlBlob2File(tmp1, cur_id_value)

Form3.PictureBox1.ImageLocation = tmp1

Form3.PictureBox1.Load()

End Sub

Sub OpenTicket(ByVal id As Integer, ByVal id_inc As String)

Form3.Visible = True

Form3.Activate()

Form3.l_id.Text = "Заявка №" & CStr(id)

Form3.l_incident.Text = "Тип инцидента - " & id_inc

Form3.tbox_ticket.Text = GetComment(id)

End Sub

Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object,

ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs)

Handles LinkLabel1.LinkClicked

Form2.Visible = True

Form2.Activate()

End Sub

Private Sub Form1_LocationChanged(ByVal sender As System.Object,

ByVal e As System.EventArgs) Handles MyBase.LocationChanged

If Me.WindowState = FormWindowState.Minimized Then

Me.Hide()

End If

End Sub

Private Sub NotifyIcon1_DoubleClick(ByVal sender As System.Object,

ByVal e As System.EventArgs) Handles NotifyIcon1.DoubleClick

Me.Show()

Me.WindowState = FormWindowState.Normal

End Sub

Private Sub Form1_FormClosed(ByVal sender As System.Object, ByVal e

As System.Windows.Forms.FormClosedEventArgs) Handles

MyBase.FormClosed

NotifyIcon1.Dispose()

End Sub

End Class

Public Class Form2

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button2.Click

If (tbox_server.Text = "") Then

tbox_server.BackColor = Color.Red

If tbox_DB.Text = "" Then tbox_DB.BackColor = Color.Red

Else

If tbox_DB.Text = "" Then

tbox_DB.BackColor = Color.Red

Else

If Form1.TestConnect(tbox_server.Text, tbox_DB.Text) Then

If tbox_refresh_t.Text = "" Then tbox_refresh_t.Text = "30"

SaveSettings(tbox_server.Text, tbox_DB.Text, tbox_refresh_t.Text,

tbox_count_tick.Text)

Me.Close()

Form1.FillForm()

Else

Dim result As MsgBoxResult = MsgBox("Не правильные настройки!

Повторить?", MsgBoxStyle.OkCancel, "Информация")

If result = MsgBoxResult.Ok Then

tbox_server.BackColor = Color.Red

tbox_DB.BackColor = Color.Red

End If

End If

End If

End If

End Sub

Private Sub Form2_FormClosed(ByVal sender As System.Object, ByVal e

As System.Windows.Forms.FormClosedEventArgs) Handles

MyBase.FormClosed

Form1.Enabled = True

End Sub

Private Sub SaveSettings(ByVal server As String, ByVal DBN As String,

ByVal refr_tm As String, ByVal c_tick As String)

Form1.srv = server

Form1.it_db = DBN

Form1.refr_time = refr_tm

Form1.ticket_count = c_tick

End Sub



Private Sub tbox_server_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles tbox_server.Click

tbox_server.BackColor = Color.White

End Sub

Private Sub tbox_DB_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles tbox_DB.Click

tbox_DB.BackColor = Color.White

End Sub

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles MyBase.Load

tbox_server.Text = Form1.srv

tbox_DB.Text = Form1.it_db

tbox_refresh_t.Text = Form1.refr_time

tbox_count_tick.Text = Form1.ticket_count

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

If Form1.TestConnect(tbox_server.Text, tbox_DB.Text) Then

MsgBox("Соединение успешно установлено", MsgBoxStyle.Information,

"Сообщение")

Else

MsgBox("Неправильные настройки", MsgBoxStyle.Information,

"Сообщение")

End If

End Sub

End Class



Public Class Form3

Dim USR As String = Split(My.User.Name, "\")(1)

Dim cur_id As Integer

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

Form1.Enabled = True

PictureBox1.Image.Dispose()

My.Computer.FileSystem.DeleteFile(Form1.tmp1)

End Sub

Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked

Form4.PictureBox1.ImageLocation = Form1.tmp1

Form4.PictureBox1.Load()

Form4.Visible = True

Form4.Activate()

End Sub

Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click

Form4.PictureBox1.ImageLocation = Form1.tmp1

Form4.PictureBox1.Load()

Form4.Visible = True

Form4.Activate()

End Sub

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 Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim selected_isp As Integer = cbox_ispoln.SelectedIndex

If selected_isp > -1 Then

cur_id = CInt(Form1.Label1.Text)

UpdateIspoln(cur_id, selected_isp)

UpdateStatus(cur_id, 2) 'назначено

UpdatePriority(cur_id, cbox_priority.SelectedIndex + 1)

MsgBox("Заявка назначена", MsgBoxStyle.Information, "Сообщение")

Form1.FillForm()

Else

MsgBox("Заявка не назначена. Не выбран исполнитель", MsgBoxStyle.Information, "Сообщение")

End If

Form1.FillDataGridView(Form1.srv, Form1.it_db, Form1.DataGridView1, "1")

Form1.FillDataGridView(Form1.srv, Form1.it_db, Form1.DataGridView2, "5")

Me.Close()

End Sub

Private Sub UpdateIspoln(ByVal id As Integer, ByVal ispoln As Integer)

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 = "UPDATE ticket" & vbCrLf & _

"set id_users = " & CStr(ispoln) & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Sub UpdateStatus(ByVal id As Integer, ByVal status As Integer)

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 = "UPDATE ticket" & vbCrLf & _

"set id_status = " & CStr(status) & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Sub UpdateDT(ByVal id As Integer, ByVal DT 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 = "UPDATE ticket" & vbCrLf & _

"set close_ticket = convert(datetime,'" & DT & "')" & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Function GetDateNowString() As String

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

Dim dn As Date = DateAndTime.Now

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

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

End Function

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

End Sub

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

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 * from admins"

SqlAdapter1.SelectCommand = SqlCommand1

SqlAdapter1.SelectCommand.Connection = SqlConnection1

SqlAdapter1.SelectCommand.Connection.Open()

SqlAdapter1.Fill(DataSet1)

Dim c_row As Integer = DataSet1.Tables.Item(0).Rows.Count

cbox_ispoln.Items.Clear()

For i = 2 To c_row

cbox_ispoln.Items.Add(DataSet1.Tables.Item(0).Rows.Item(i - 1).Item(1))

Next

SqlAdapter1.SelectCommand.Connection.Close()

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim SqlConnection2 As New SqlClient.SqlConnection

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = "select name_priority from priority"

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

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

cbox_priority.Items.Clear()

For i = 1 To c_row

cbox_priority.Items.Add(DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(0))

Next

SqlAdapter2.SelectCommand.Connection.Close()

Dim SqlAdapter3 As New SqlClient.SqlDataAdapter

Dim DataSet3 As New DataSet

Dim SqlConnection3 As New SqlClient.SqlConnection

SqlConnection3.ConnectionString = con_string

Dim SqlCommand3 As New SqlClient.SqlCommand

SqlCommand3.CommandText = "SELECT [id_priority]" & vbCrLf & _

"FROM [ticket] where id_ticket = " & Form1.Label1.Text

SqlAdapter3.SelectCommand = SqlCommand3

SqlAdapter3.SelectCommand.Connection = SqlConnection3

SqlAdapter3.SelectCommand.Connection.Open()

SqlAdapter3.Fill(DataSet3)

Dim npriority As String = DataSet3.Tables.Item(0).Rows.Item(0).Item(0)

SqlAdapter3.SelectCommand.Connection.Close()

cbox_priority.SelectedIndex = npriority - 1

End Sub

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

Dim selected_pri As Integer = cbox_priority.SelectedIndex

If selected_pri > -1 Then

cur_id = CInt(Form1.Label1.Text)

UpdatePriority(cur_id, selected_pri + 1)

MsgBox("Приоритет изменен", MsgBoxStyle.Information, "Сообщение")

Else

MsgBox("Приоритет не изменен. Не выбран приоритет", MsgBoxStyle.Information, "Сообщение")

End If

End Sub

Private Sub UpdatePriority(ByVal id As Integer, ByVal priority As Integer)

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 = "UPDATE ticket" & vbCrLf & _

"set id_priority = " & CStr(priority) & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Sub LinkLabel2_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel2.LinkClicked

SetOtklon(CInt(Form1.Label1.Text))

MsgBox("Заявка отклонена", MsgBoxStyle.Information, "Сообщение")

End Sub

Private Sub SetOtklon(ByVal id As Integer)

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 = "UPDATE ticket" & vbCrLf & _

"set otklon = 1 " & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

Me.Close()

Form1.FillForm()

End Sub

End Class

Public Class Form4

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

Form3.Enabled = True

PictureBox1.Image.Dispose()

End Sub

End Class

«Заявки - Исполнитель»

Public Class Form1

Dim server As String

Dim StartDate As String = "19811121 00:00:00"

Dim PC As String = My.Computer.Name

Dim USR As String = Split(My.User.Name, "\")(1)

Dim img_path As String = My.Computer.FileSystem.SpecialDirectories.Temp

Public tmp1 As String

Public srv As String = Split(GetSettings(), "%")(0)

Public it_db As String = Split(GetSettings(), "%")(1)

Public refr_time As String = Split(GetSettings(), "%")(2)

Public ticket_count As String = Split(GetSettings(), "%")(3)

Dim c1, c2, c3 As Integer

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

Me.MaximizeBox = False

Timer1.Interval = CInt(refr_time) * 1000

If srv <> "1" And it_db <> "1" Then

If TestConnect(srv, "master") Then

If TestConnect(srv, it_db) Then

FillForm()

c1 = DataGridView1.RowCount

c2 = DataGridView2.RowCount

c3 = DataGridView3.RowCount

Else

MsgBox("Не найдена база данных. Обратитесь к администратору за именем базы данных", MsgBoxStyle.Information, "Информация")

Dim result As MsgBoxResult = MsgBox("Изменить базу данных?", MsgBoxStyle.OkCancel, "Информация")

If result = MsgBoxResult.Ok Then

Form2.Visible = True

Me.Enabled = False

End If

End If

Else

MsgBox("Не правильные настройки. SQL Server не найден", MsgBoxStyle.Information, "Информация")

Dim result As MsgBoxResult = MsgBox("Выполнить настройку?", MsgBoxStyle.OkCancel, "Сообщение")

If result = MsgBoxResult.Ok Then

Form2.Visible = True

Me.Enabled = False

End If

End If

Else

Dim result As MsgBoxResult = MsgBox("Программа не настроена. Настроить?", MsgBoxStyle.OkCancel, "Сообщение")

If result = MsgBoxResult.Ok Then

Form2.Visible = True

Me.Enabled = False

End If

End If

End Sub

Sub FillForm()

Dim n1, n2, n3 As Integer

FillDataGridView(srv, it_db, DataGridView1, "3")

n1 = DataGridView1.RowCount

TabControl1.TabPages.Item(0).Text = "Высокий (" & CStr(n1) & ")"

Try

If CStr(DataGridView1.Item(0, 0).Value) = "" Then

TabControl1.TabPages.Item(0).Text = "Высокий (0)"

End If

Catch ex As Exception

End Try

FillDataGridView(srv, it_db, DataGridView2, "2")

n2 = DataGridView2.RowCount

TabControl1.TabPages.Item(1).Text = "Нормальный (" & CStr(n2) & ")"

Try

If CStr(DataGridView2.Item(0, 0).Value) = "" Then

TabControl1.TabPages.Item(1).Text = "Нормальный (0)"

End If

Catch ex As Exception

End Try

FillDataGridView(srv, it_db, DataGridView3, "1")

n3 = DataGridView3.RowCount

TabControl1.TabPages.Item(2).Text = "Низкий (" & CStr(n3) & ")"

Try

If CStr(DataGridView3.Item(0, 0).Value) = "" Then

TabControl1.TabPages.Item(2).Text = "Низкий (0)"

End If

Catch ex As Exception

End Try

End Sub

Function GetSettings() As String

Dim s As String = "1%1%30%10"

Dim r As Object

If My.Computer.FileSystem.FileExists(My.Application.Info.DirectoryPath & "\settings.ini") Then

r = My.Computer.FileSystem.OpenTextFileReader(My.Application.Info.DirectoryPath & "\settings.ini")

s = r.ReadLine

r.Close()

If s = "" Then s = "1%1%30%10"

End If

GetSettings = s

End Function

Function TestConnect(ByVal server As String, ByVal DBN As String) As Boolean

Dim con_string As String = "Data Source=" & server & ";Initial Catalog=" & DBN & ";Integrated Security=True"

Dim result As String = ""

Try

Dim cn As New SqlClient.SqlConnection(con_string)

cn.Open()

cn.Close()

Catch ex As Exception

result = ex.Message

End Try

If result = "" Then

TestConnect = True

Else

TestConnect = False

End If

End Function

Private Sub SqlBlob2File(ByVal DestFilePath As String, ByVal id As Integer)

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

Dim PictureCol As Integer = 0

Dim cn As New SqlClient.SqlConnection(con_string)

Dim cmd As New SqlClient.SqlCommand("SELECT image FROM attach WHERE id_ticket=" & CStr(id), cn)

cn.Open()

Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader()

dr.Read()

Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte

dr.GetBytes(PictureCol, 0, b, 0, b.Length)

dr.Close()

cn.Close()

Dim fs As New System.IO.FileStream(DestFilePath, IO.FileMode.Create, IO.FileAccess.Write)

fs.Write(b, 0, b.Length)

fs.Close()

End Sub

Private Function GetComment(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" & 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 GetIncident(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" & 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 GetStatus(ByVal id As Integer) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" & 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_status]" & _

"FROM [status] where id_status = " & 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)

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

SqlAdapter2.SelectCommand.Connection.Close()

End Function

Private Function GetUsersID(ByVal admin As String) As String

Dim con_string As String = "Data Source=" & srv & ";Initial Catalog=" & 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 [id_users]" & _

"FROM [admins] where [login] like '" & CStr(admin) & "'"

Try

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)

GetUsersID = CStr(DataSet2.Tables.Item(0).Rows.Item(0).Item(0))

SqlAdapter2.SelectCommand.Connection.Close()

Catch ex As Exception

End Try

End Function

Private Sub FillDataGridView(ByVal server As String, ByVal DBN As String, ByRef DGV As DataGridView, ByVal priority As String)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim con_string As String = "Data Source=" & server & ";Initial Catalog=" & DBN & ";Integrated Security=True"

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim c_row, c_col As Integer

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

",[fio]" & _

",[phone]" & _

",[pc_inv]" & _

",[room]" & _

",[open_ticket]" & _

",[id_incident]" & _

"FROM [ticket] WHERE [id_users] = " & GetUsersID(USR) & " and [deleted] = 0 and [otklon] = 0 and [id_status] > 1 and [id_status] < 5 and [id_priority] = " & _

priority

Try

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)

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

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

If c_row > 0 Then

DGV.RowCount = c_row

DGV.ColumnCount = c_col

DGV.Columns.Item(0).HeaderText = "Номер заявки"

DGV.Columns.Item(1).HeaderText = "ФИО"

DGV.Columns.Item(2).HeaderText = "Телефон"

DGV.Columns.Item(3).HeaderText = "Имя компьютера"

DGV.Columns.Item(4).HeaderText = "Кабинет"

DGV.Columns.Item(5).HeaderText = "Открыта"

DGV.Columns.Item(6).HeaderText = "Тип инцидента"

For i = 1 To c_row

For j = 1 To c_col

If j = 7 Then

DGV.Item(j - 1, i - 1).Value =

GetIncident(DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1))

Else

If j = 0 Then DGV.Item(j - 1, i - 1).Value =

DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1)

DGV.Item(j - 1, i - 1).Value = DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1).ToString

End If

Next

Next

Else

DGV.Rows.Clear()

DGV.RowCount = 0

End If

SqlAdapter2.SelectCommand.Connection.Close()

Catch ex As Exception

End Try

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Timer1.Tick

Dim m1, m2, m3 As String

Dim rc1, rc2, rc3 As Integer

FillForm()

rc1 = DataGridView1.RowCount

rc2 = DataGridView2.RowCount

rc3 = DataGridView3.RowCount

If c1 < rc1 Or c2 < rc2 Or c3 < rc3 Then

If c1 < rc1 Then

m1 = vbTab & "Высокий приоритет - " & CStr(rc1 - c1) & "(" & rc1 & ")" & vbCrLf

c1 = rc1

Else

m1 = ""

End If

If c2 < rc2 Then

m2 = vbTab & "Нормальный приоритет - " & CStr(rc2 - c2) & "(" & rc2 & ")" & vbCrLf

c2 = rc2

Else

m2 = ""

End If

If c3 < rc3 Then

m3 = vbTab & "Низкий приоритет - " & CStr(rc3 - c3) & "(" & rc3 & ")"

c3 = rc3

Else

m3 = ""

End If

If Me.WindowState = FormWindowState.Minimized Then

Me.Show()

Me.WindowState = FormWindowState.Normal

End If

NotifyIcon1.ShowBalloonTip(5000, "ticket_admin", "НОВЫЕ ЗАЯВКИ:" & vbTab & vbTab & vbCrLf & _

m1 & m2 & m3, ToolTipIcon.Info)

End If

End Sub

Private Sub DataGridView1_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick, DataGridView3.CellDoubleClick, DataGridView2.CellDoubleClick

Dim cur_row As Integer = sender.CurrentRow.Index

If CStr(sender.Item(0, 0).Value) <> "" Then

If cur_row > -1 And sender.RowCount > 0 Then

Dim cur_id_value As Integer = sender.Item(0, cur_row).Value

Dim cur_inc_value As String = sender.Item(6, cur_row).Value

Form3.L_user.Text = "Пользователь - " & sender.Item(1, cur_row).Value & _

" Тел. " & sender.Item(2, cur_row).Value & _

" Каб. " & sender.Item(4, cur_row).Value

OpenTicket(cur_id_value, cur_inc_value)

tmp1 = img_path & "\image_z_" & CStr(cur_id_value) & ".jpg"

SqlBlob2File(tmp1, cur_id_value)

Form3.PictureBox1.ImageLocation = tmp1

Form3.PictureBox1.Load()

End If

End If

End Sub

Sub OpenTicket(ByVal id As Integer, ByVal id_inc As String)

Form3.Visible = True

Form3.Activate()

Form3.l_id.Text = "Заявка №" & CStr(id)

Form3.l_incident.Text = "Тип инцидента - " & id_inc

Form3.tbox_ticket.Text = GetComment(id)

End Sub

Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked

Form2.Visible = True

Form2.Activate()

End Sub

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

NotifyIcon1.Dispose()

Dim wr As Object

If My.Computer.FileSystem.FileExists(My.Application.Info.DirectoryPath & "\settings.ini") Then

My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\settings.ini")

End If

wr = My.Computer.FileSystem.OpenTextFileWriter(My.Application.Info.DirectoryPath & "\settings.ini", False)

wr.WriteLine(srv & "%" & it_db & "%" & refr_time & "%" & ticket_count)

wr.Close()

End Sub

Private Sub NotifyIcon1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NotifyIcon1.DoubleClick

Me.Show()

Me.WindowState = FormWindowState.Normal

End Sub

Private Sub Form1_LocationChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LocationChanged

If Me.WindowState = FormWindowState.Minimized Then

Me.Hide()

End If

End Sub

End Class

Public Class Form2

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

If (tbox_server.Text = "") Then

tbox_server.BackColor = Color.Red

If tbox_DB.Text = "" Then tbox_DB.BackColor = Color.Red

Else

If tbox_DB.Text = "" Then

tbox_DB.BackColor = Color.Red

Else

If Form1.TestConnect(tbox_server.Text, tbox_DB.Text) Then

If tbox_refresh_t.Text = "" Then tbox_refresh_t.Text = "30"

SaveSettings(tbox_server.Text, tbox_DB.Text, tbox_refresh_t.Text, tbox_count_tick.Text)

Me.Close()

Form1.FillForm()

Else

Dim result As MsgBoxResult = MsgBox("Не правильные настройки! Повторить?", MsgBoxStyle.OkCancel, "Информация")

If result = MsgBoxResult.Ok Then

tbox_server.BackColor = Color.Red

tbox_DB.BackColor = Color.Red

End If

End If

End If

End If

End Sub

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

Form1.Enabled = True

End Sub

Private Sub SaveSettings(ByVal server As String, ByVal DBN As String, ByVal refr_tm As String, ByVal c_tick As String)

Form1.srv = server

Form1.it_db = DBN

Form1.refr_time = refr_tm

Form1.ticket_count = c_tick

End Sub

Private Sub tbox_server_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tbox_server.Click

tbox_server.BackColor = Color.White

End Sub

Private Sub tbox_DB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tbox_DB.Click

tbox_DB.BackColor = Color.White

End Sub

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

tbox_server.Text = Form1.srv

tbox_DB.Text = Form1.it_db

tbox_refresh_t.Text = Form1.refr_time

tbox_count_tick.Text = Form1.ticket_count

End Sub

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

If Form1.TestConnect(tbox_server.Text, tbox_DB.Text) Then

MsgBox("Соединение успешно установлено", MsgBoxStyle.Information, "Сообщение")

Else

MsgBox("Неправильные настройки", MsgBoxStyle.Information, "Сообщение")

End If

End Sub

End Class

Public Class Form3

Dim USR As String = Split(My.User.Name, "\")(1)

Dim cur_id As Integer

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

Form1.Enabled = True

PictureBox1.Image.Dispose()

My.Computer.FileSystem.DeleteFile(Form1.tmp1)

Timer1.Enabled = False

UpdateStatus(cur_id, 4) 'отложить

End Sub

Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked

Form4.PictureBox1.ImageLocation = Form1.tmp1

Form4.PictureBox1.Load()

Form4.Visible = True

Form4.Activate()

End Sub

Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click

Form4.PictureBox1.ImageLocation = Form1.tmp1

Form4.PictureBox1.Load()

Form4.Visible = True

Form4.Activate()

End Sub

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 Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If tbox_otvet.Text <> "" Then

UpdateComment(cur_id)

tbox_otvet.Text = ""

End If

End Sub

Private Sub UpdateComment(ByVal id As Integer)

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 = "UPDATE comments" & vbCrLf & _

"SET comments = '" & tbox_ticket.Text & vbCrLf & _

"----" & USR & "----" & vbCrLf & _

tbox_otvet.Text & "'" & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

tbox_ticket.Text = GetComment(cur_id)

End Sub

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

UpdateStatus(cur_id, 4)

Me.Close()

End Sub

Private Sub UpdateStatus(ByVal id As Integer, ByVal status As Integer)

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 = "UPDATE ticket" & vbCrLf & _

"set id_status = " & CStr(status) & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

Private Sub UpdateDT(ByVal id As Integer, ByVal DT 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 = "UPDATE ticket" & vbCrLf & _

"set close_ticket = convert(datetime,'" & DT & "')" & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

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

Timer1.Enabled = True

cur_id = CInt(Split(l_id.Text, "№")(1))

UpdateStatus(cur_id, 3)

End Sub

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

If tbox_otvet.Text <> "" Then

UpdateComment(cur_id)

tbox_otvet.Text = ""

Else

tbox_otvet.Text = "-------"

UpdateComment(cur_id)

tbox_otvet.Text = ""

End If

Me.Close()

UpdateDT(cur_id, GetDateNowString())

UpdateStatus(cur_id, 5)

UpdateWT(cur_id)

End Sub

Private Function GetDateNowString() As String

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

Dim dn As Date = DateAndTime.Now

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

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

End Function

Private Sub UpdateWT(ByVal id As Integer)

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 = _

"UPDATE [ticket]" & vbCrLf & _

"SET [work_time] = (select [close_ticket]-[open_ticket] from [ticket] where id_ticket = " & CStr(id) & ")" & vbCrLf & _

"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)

SqlAdapter2.SelectCommand.Connection.Close()

End Sub

End Class

Public Class Form4

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

Form3.Enabled = True

PictureBox1.Image.Dispose()

End Sub

End Class

«Заявки - Настройка»

Public Class Form1

Dim server As String

Dim StartDate As String = "19811121 00:00:00"

Dim PC As String = My.Computer.Name

Dim USR As String = Split(My.User.Name, "\")(1)

Dim img_path As String = My.Computer.FileSystem.SpecialDirectories.Temp

Public tmp1 As String

Public srv As String = Split(GetSettings(), "%")(0)

Public it_db As String = Split(GetSettings(), "%")(1)

Public refr_time As String = Split(GetSettings(), "%")(2)

Public ticket_count As String = Split(GetSettings(), "%")(3)

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

Form2.Visible = True

Form2.Activate()

End Sub

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

Form3.Visible = True

Form3.Activate()

End Sub

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

Form4.Visible = True

Form4.Activate()

End Sub

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

Form5.Visible = True

Form5.Activate()

Form5.TextBox1.Text = srv

Form5.TextBox2.Text = it_db

End Sub

Function GetSettings() As String

Dim s As String = "1%1%30%10"

Dim r As Object

If My.Computer.FileSystem.FileExists(My.Application.Info.DirectoryPath & "\settings.ini") Then

r = My.Computer.FileSystem.OpenTextFileReader(My.Application.Info.DirectoryPath & "\settings.ini")

s = r.ReadLine

r.Close()

If s = "" Then s = "1%1%30%10"

End If

GetSettings = s

End Function

Function TestConnect(ByVal server As String, ByVal DBN As String) As Boolean

Dim con_string As String = "Data Source=" & server & ";Initial Catalog=" & DBN & ";Integrated Security=True"

Dim result As String = ""

Try

Dim cn As New SqlClient.SqlConnection(con_string)

cn.Open()

cn.Close()

Catch ex As Exception

result = ex.Message

End Try

If result = "" Then

TestConnect = True

Else

TestConnect = False

End If

End Function

Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

Dim wr As Object

If My.Computer.FileSystem.FileExists(My.Application.Info.DirectoryPath & "\settings.ini") Then

My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\settings.ini")

End If

wr = My.Computer.FileSystem.OpenTextFileWriter(My.Application.Info.DirectoryPath & "\settings.ini", False)

wr.WriteLine(srv & "%" & it_db & "%" & refr_time & "%" & ticket_count)

wr.Close()

End Sub

End Class

Public Class Form2

Private Sub Form2_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 Form2_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form1.Visible = False

End Sub

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

FillDGVInc(Form1.srv, Form1.it_db, DataGridView1)

End Sub

Private Sub FillDGVInc(ByVal server As String, ByVal DBN As String, ByRef DGV As DataGridView)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim con_string As String = "Data Source=" & server & ";Initial Catalog=" & DBN & ";Integrated Security=True"

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim c_row, c_col As Integer

Dim cmd As String = "SELECT [id_incident],[name_incident] FROM [incident]"

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

Try

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

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

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

If c_row > 0 Then

DGV.RowCount = c_row

DGV.ColumnCount = c_col

DGV.Columns.Item(0).HeaderText = "Номер"

DGV.Columns.Item(1).HeaderText = "Инцидент"

For i = 1 To c_row

For j = 1 To c_col

DGV.Item(j - 1, i - 1).Value = DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1).ToString

Next

Next

End If

SqlAdapter2.SelectCommand.Connection.Close()

Catch ex As Exception

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

Me.Close()

End Try

End Sub

Private Sub TextBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.Click

TextBox1.BackColor = Color.White

TextBox1.Text = ""

TextBox1.ForeColor = Color.Black

End Sub

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

If TextBox1.Text = "" Then

TextBox1.BackColor = Color.Red

TextBox1.Text = "Введите инцидент"

TextBox1.ForeColor = Color.White

Else

AddIncident(TextBox1.Text)

FillDGVInc(Form1.srv, Form1.it_db, DataGridView1)

MsgBox("Инцидент добавлен", MsgBoxStyle.Information, "Сообщение")

TextBox1.Text = ""

End If

End Sub

Private Sub AddIncident(ByVal incident As String)

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

Dim cmd As String = _

"INSERT" & vbCrLf & _

"INTO incident(name_incident)" & vbCrLf & _

"VALUES ('" & incident & "')"

SqlCommand1.CommandText = cmd

SqlAdapter1.InsertCommand = SqlCommand1

SqlAdapter1.InsertCommand.Connection = SqlConnection1

Try

SqlAdapter1.InsertCommand.Connection.Open()

SqlAdapter1.InsertCommand.ExecuteNonQuery()

SqlAdapter1.InsertCommand.Connection.Close()

Catch ex As Exception

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

End Try

End Sub

End Class

Public Class Form3

Private Sub Form3_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 Form3_Shown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown

Form1.Visible = False

End Sub

Private Sub TextBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.Click

TextBox1.BackColor = Color.White

TextBox1.Text = ""

TextBox1.ForeColor = Color.Black

End Sub

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

If TextBox1.Text = "" Or TextBox2.Text = "" Then

If TextBox1.Text = "" Then

TextBox1.BackColor = Color.Red

TextBox1.Text = "Введите исполнителя"

TextBox1.ForeColor = Color.White

End If

If TextBox2.Text = "" Then

TextBox2.BackColor = Color.Red

TextBox2.Text = "Введите логин"

TextBox2.ForeColor = Color.White

End If

Else

AddIspoln(TextBox1.Text, TextBox2.Text)

FillDGVIsp(Form1.srv, Form1.it_db, DataGridView1)

MsgBox("Исполнитель добавлен", MsgBoxStyle.Information, "Сообщение")

TextBox1.Text = ""

TextBox2.Text = ""

End If

End Sub

Private Sub AddIspoln(ByVal ispoln As String, ByVal isp_l As String)

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

Dim cmd As String = _

"INSERT" & vbCrLf & _

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

"VALUES ('" & ispoln & "','" & isp_l & "')"

SqlCommand1.CommandText = cmd

SqlAdapter1.InsertCommand = SqlCommand1

SqlAdapter1.InsertCommand.Connection = SqlConnection1

SqlAdapter1.InsertCommand.Connection.Open()

SqlAdapter1.InsertCommand.ExecuteNonQuery()

SqlAdapter1.InsertCommand.Connection.Close()

End Sub

Private Sub FillDGVIsp(ByVal server As String, ByVal DBN As String, ByRef DGV As DataGridView)

Dim SqlAdapter2 As New SqlClient.SqlDataAdapter

Dim DataSet2 As New DataSet

Dim con_string As String = "Data Source=" & server & ";Initial Catalog=" & DBN & ";Integrated Security=True"

Dim SqlConnection2 As New SqlClient.SqlConnection

Dim c_row, c_col As Integer

Dim cmd As String = "SELECT [id_users],[admin],[login] FROM [admins]"

SqlConnection2.ConnectionString = con_string

Dim SqlCommand2 As New SqlClient.SqlCommand

SqlCommand2.CommandText = cmd

SqlAdapter2.SelectCommand = SqlCommand2

SqlAdapter2.SelectCommand.Connection = SqlConnection2

Try

SqlAdapter2.SelectCommand.Connection.Open()

SqlAdapter2.Fill(DataSet2)

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

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

If c_row > 0 Then

DGV.RowCount = c_row

DGV.ColumnCount = c_col

DGV.Columns.Item(0).HeaderText = "Номер"

DGV.Columns.Item(1).HeaderText = "Исполнитель"

DGV.Columns.Item(2).HeaderText = "Логин"

For i = 1 To c_row

For j = 1 To c_col

DGV.Item(j - 1, i - 1).Value = DataSet2.Tables.Item(0).Rows.Item(i - 1).Item(j - 1).ToString

Next

Next

End If

SqlAdapter2.SelectCommand.Connection.Close()

Catch ex As Exception

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

Me.Close()

End Try

End Sub

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

FillDGVIsp(Form1.srv, Form1.it_db, DataGridView1)

End Sub

Private Sub TextBox2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.Click

TextBox2.BackColor = Color.White

TextBox2.Text = ""

TextBox2.ForeColor = Color.Black

End Sub

End Class

Imports Microsoft.Office.Interop

Public Class Form4

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