109
«Приложение 1». Создание базы данных
create database NBHelpDesk
use NBHelpdesk
Create table Specialization (
Specialization_ID integer not null Identity(1,1),
Specialization_Name nvarchar (50) not null,
Constraint PK_Specialization Primary key (Specialization_ID),
);
Create table Position (
Position_ID integer not null Identity(1,1),
Position_Name nvarchar (70) not null,
Constraint PK_Position Primary key (Position_ID),
);
Create table Condition (
Condition_ID integer not null Identity(1,1),
Condition_Name nvarchar (50) not null,
Constraint PK_Condition Primary key (Condition_ID),
);
Create table Privilege (
Privilege_ID integer not null Identity(1,1),
Privilege_Name nvarchar (50) not null,
Constraint PK_Privilege Primary key (Privilege_ID),
);
110
Create table ProblemType (
ProblemType_ID integer not null Identity(1,1),
ProblemType_Name nvarchar (50) not null,
Constraint PK_ProblemType Primary key (ProblemType_ID),
);
Create table Origin (
Origin_ID integer not null Identity(1,1),
Origin_Name nvarchar (50) not null,
Constraint PK_Origin Primary key (Origin_ID),
);
Create table Department (
Department_ID integer not null Identity(1,1),
Department_Name nvarchar (50) not null,
Constraint PK_Department Primary key (Department_ID),
);
Create table MessageType (
MessageType_ID integer not null Identity(1,1),
MessageType_Name nvarchar (50) not null,
Constraint PK_MessageType Primary key (MessageType_ID),
);
Create table SoftType (
SoftType_ID integer not null Identity(1,1),
SoftType_Name nvarchar (70) not null,
Constraint PK_SoftType Primary key (SoftType_ID),
);
111
Create table Room (
Room_ID integer not null Identity(1,1),
Room_Name nvarchar (40) not null,
Room_Cooment nvarchar (100),
Constraint PK_Room Primary key (Room_ID),
);
Create table Soft (
Soft_ID integer not null Identity(1,1),
Soft_Name nvarchar (70) not null,
Soft_Comment nvarchar (200),
Soft_SoftType_FK integer not null,
Constraint PK_Soft Primary key (Soft_ID),
Constraint FK_Soft Foreign key (Soft_SoftType_FK) References SoftType
(SoftType_ID)
on DELETE Cascade on UPDATE Cascade
);
Create table Employee (
Employee_ID integer not null Identity(1,1),
Employee_FirstName nvarchar (70) not null,
Employee_SecondName nvarchar (70) not null,
Employee_LastName nvarchar (70),
Employee_Login nvarchar (70) not null,
Employee_Email nvarchar (70) not null,
Employee_Phone smallint not null,
Employee_Mobile nvarchar (10) not null,
Employee_Comment nvarchar (100),
Employee_Department_FK integer not null,
Employee_Room_FK integer not null,
112
Employee_Position_FK integer not null,
Constraint PK_Employee Primary key (Employee_ID),
Constraint FK1_Employee Foreign key (Employee_Department_FK) References
Department (Department_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK2_Employee Foreign key (Employee_Room_FK) References Room
(Room_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK3_Employee Foreign key (Employee_Position_FK) References
Position (Position_ID)
on DELETE Cascade on UPDATE Cascade
);
Create table Technician (
Technician_ID integer not null Identity(1,1),
Technician_FirstName nvarchar (70) not null,
Technician_SecondName nvarchar (70) not null,
Technician_LastName nvarchar (70),
Technician_JobEmail nvarchar (70) not null,
Technician_PrivateEmail nvarchar (70) not null,
Technician_Phone smallint not null,
Technician_Mobile nvarchar (10) not null,
Technician_Holiday bit not null,
Technician_Comment nvarchar (100),
Technician_Position_FK integer not null,
Constraint PK_Technician Primary key (Technician_ID),
Constraint FK_Technician Foreign key (Technician_Position_FK) References
Position (Position_ID)
on DELETE Cascade on UPDATE Cascade
);
113
Create table Request (
Request_ID integer not null Identity(1,1),
Request_OpenDate DateTime not null,
Request_CloseDate DateTime,
Request_DeadLine DateTime,
Request_Title nvarchar (100) not null,
Request_RecievedTime DateTime,
Request_Condition_FK integer not null,
Request_Privilege_FK integer not null,
Request_ProblemType_FK integer not null,
Request_Origin_FK integer not null,
Request_Employee_FK integer not null,
Request_Technician_FK integer not null,
Request_FAQ bit not null,
Constraint PK_Request Primary key (Request_ID),
Constraint FK1_Request Foreign key (Request_Condition_FK) References
Condition (Condition_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK2_Request Foreign key (Request_Privilege_FK) References
Privilege (Privilege_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK3_Request Foreign key (Request_ProblemType_FK) References
ProblemType (ProblemType_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK4_Request Foreign key (Request_Origin_FK) References Origin
(Origin_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK5_Request Foreign key (Request_Employee_FK) References
Employee (Employee_ID)
on DELETE NO ACTION on UPDATE NO ACTION,
Constraint FK6_Request Foreign key (Request_Technician_FK) References
Technician (Technician_ID)
114
on DELETE NO ACTION on UPDATE NO ACTION
);
Create table SoftEmployee (
SoftEmployee_Comment nvarchar (200),
SoftEmployee_Soft_FK integer not null,
SoftEmployee_Employee_FK integer not null,
Constraint PK_SoftEmployee Primary key
(SoftEmployee_Soft_FK,SoftEmployee_Employee_FK),
Constraint FK1_SoftEmployee Foreign key (SoftEmployee_Soft_FK) References
Soft (Soft_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK2_SoftEmployee Foreign key (SoftEmployee_Employee_FK)
References Employee (Employee_ID)
on DELETE Cascade on UPDATE Cascade
);
Create table MessageReq (
MessageReq_ID integer not null Identity(1,1),
MessageReq_Text nvarchar (2000),
MessageReq_Request_FK integer not null,
MessageReq_MessageType_FK integer not null,
Constraint PK_MessageReq Primary key (MessageReq_ID),
Constraint FK1_MessageReq_FK Foreign key (MessageReq_Request_FK)
References Request (Request_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK2_MessageReq_FK Foreign key (MessageReq_MessageType_FK)
References MessageType (MessageType_ID)
on DELETE Cascade on UPDATE Cascade
);
Create table Spec (
115
Spec_Comment nvarchar (200),
Spec_Technician_FK integer not null,
Spec_Specialization_FK integer not null,
Constraint PK_Spec Primary key
(Spec_Technician_FK,Spec_Specialization_FK),
Constraint FK1_Spec Foreign key (Spec_Technician_FK) References Technician
(Technician_ID)
on DELETE Cascade on UPDATE Cascade,
Constraint FK2_Spec Foreign key (Spec_Specialization_FK) References
Specialization (Specialization_ID)
on DELETE Cascade on UPDATE Cascade
);
116
«Приложение 2». Листинг приложения
'ModuleNBHelpDesk
Imports System
Imports System.Data
Imports System.Data.SqlClient
Module ModuleNBHelpDesk
'Modify the following code to correctly connect to your SQL Server.
Public sConnectionString As String = "Data Source=s001;Initial
Catalog=NBHelpDesk;User ID=alex;Password=alex; connection timeout=5"
Public objConn As New SqlConnection(sConnectionString)
Public connectstatus As Boolean = True
Public IdTemp As String = ""
Sub ConnectToBD()
Try
objConn.Open()
Catch
MsgBox("Ошибка соединения с базой")
connectstatus = False
End Try
End Sub
Sub DisconnectFromBD()
Try
objConn.Close()
Catch
MsgBox("Ошибка закрытия соединения с базой")
End Try
End Sub
End Module
'RibbonNBHD
Imports Microsoft.Office.Tools.Ribbon
117
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class RibbonNBHD
Private Sub RibbonNBHD_Load(ByVal sender As System.Object, ByVal e As
RibbonUIEventArgs) Handles MyBase.Load
Dim conn As Boolean = True 'соединение есть
Dim objConn As New SqlConnection(sConnectionString)
Try
objConn.Open()
Catch
'MsgBox("Ошибка соединения с базой")
conn = False 'соединения нет
GroupEmp.Visible = False
GroupTech.Visible = False
GroupNoWork.Visible = True
GroupNoWork.Label = "Нет соединения с базой"
End Try
If conn Then
Dim olApp As Outlook.Application
olApp = CreateObject("Outlook.Application")
Dim accounts As Outlook.Accounts = olApp.Session.Accounts()
Dim account As Outlook.Account
Dim i As Integer = 0
'ищем учетку exchange
If olApp.Session.Accounts.Count > 0 Then
For Each account In accounts
i = i + 1
If account.AccountType = Outlook.OlAccountType.olExchange Then
Exit For
118
End If
Next
Else
GroupEmp.Visible = False
GroupTech.Visible = False
GroupNoWork.Visible = True
GroupNoWork.Label = "нет учеток"
End If
account = accounts.Item(2) ' Для тестов
'account = accounts.Item(i)
Dim SqlReader As SqlDataReader
Dim sqlcmd As New SqlCommand
sqlcmd.CommandText = "select Employee_Email from Employee where
Employee_Email='" & account.SmtpAddress.ToString() & "'"
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
GroupEmp.Visible = True
GroupTech.Visible = False
GroupNoWork.Visible = False
Else
SqlReader.Close()
sqlcmd.CommandText = "select Technician_JobEmail from Technician where
Technician_JobEmail='" & account.SmtpAddress.ToString() & "'"
sqlcmd.Connection = objConn
SqlReader = sqlcmd.ExecuteReader()
If SqlReader.HasRows Then
GroupEmp.Visible = False
GroupTech.Visible = True
GroupNoWork.Visible = False
Else