public ObservableCollection <VTUPS> GetAll(int regionId)
{
ObservableCollection <VTUPS> vtUPSes = new ObservableCollection <VTUPS>();
DataTable vtUPSTable = (regionId != 25) ? DatabaseUtility.GetTable("SELECT * FROM v_GetUPSes WHERE OwnerRegion IN (" + regionId + ")") :
DatabaseUtility.GetTable("SELECT * FROM v_GetUPSes");
foreach (DataRow row in vtUPSTable.Rows)
{
VTUPS vtUPS = new VTUPS()
{
Id = row.Field <int>("Id"),
UPSId = row.Field <int>("UPSId"),
UPS = new UPS()
{
Id = row.Field <int>("UPSId"),
NameUPS = row.Field <string>("NameUPS")
},
// ниже общие поля для всех моделей VT
YearUsingSince = row.Field <int>("YearUsingSince"),
InventoryNo = row.Field <string>("InventoryNo"),
SerialNumber = row.Field <string>("SerialNumber"),
Room = row.Field <string>("Room"),
Note = row.Field <string>("Note"),
OwnerRegion = row.Field <int>("OwnerRegion"),
InUse = row.Field <bool>("InUse")
};
vtUPSes.Add(vtUPS);
}
return(vtUPSes);
}
public ObservableCollection <VTComputer> GetAll(int regionId)
{
ObservableCollection <VTComputer> vtComputers = new ObservableCollection <VTComputer>();
DataTable computersTable = (regionId != 25) ?
DatabaseUtility.GetTable("SELECT * FROM v_GetComputers WHERE OwnerRegion IN (" + regionId + ")") :
DatabaseUtility.GetTable("SELECT * FROM v_GetComputers");
foreach (DataRow row in computersTable.Rows)
{
VTComputer vtComputer = new VTComputer
{
Id = row.Field <int>("Id"),
BoardId = row.Field <int>("BoardId"),
Board = new Board()
{
Id = row.Field <int>("BoardId"), Motherboard = row.Field <string>("Motherboard")
},
CpuId = row.Field <int>("CPUId"),
Cpu = new CPU()
{
Id = row.Field <int>("BoardId"), NameCPU = row.Field <string>("NameCPU")
},
CpuClockSpeed = row.Field <int>("CpuClockSpeed"),
HddId = row.Field <int>("HddId"),
Hdd = new HDD()
{
Id = row.Field <int>("HddId"), NameHDD = row.Field <string>("NameHDD")
},
HddCapacity = row.Field <int>("HddCapacity"),
RamCapacity = row.Field <double>("RamCapacity"),
YearUsingSince = row.Field <int>("YearUsingSince"),
InventoryNo = row.Field <string>("InventoryNo"),
Hostname = row.Field <string>("HostName"),
IpAddress = row.Field <string>("Ip"),
WorkerName = row.Field <string>("WorkerName"),
Room = row.Field <string>("Room"),
OsId = row.Field <int>("OSId"),
OS = new OS()
{
Id = row.Field <int>("OSId"), NameOS = row.Field <string>("NameOS")
},
LicenseId = row.Field <int>("LicenseStateId"),
License = new License()
{
Id = row.Field <int>("LicenseStateId"), LicenseState = row.Field <string>("License")
},
Note = row.Field <string>("Note"),
InUse = row.Field <bool>("InUse")
};
vtComputers.Add(vtComputer);
}
return(vtComputers);
}
public ObservableCollection <HDD> GetAll()
{
ObservableCollection <HDD> hdds = new ObservableCollection <HDD>();
DataTable HDDsTable = DatabaseUtility.GetTable("SELECT * FROM BookHdd");
foreach (DataRow row in HDDsTable.Rows)
{
hdds.Add(new HDD()
{
Id = row.Field <int>("Id"),
NameHDD = row.Field <string>("NameHDD")
});
}
return(hdds);
}
public ObservableCollection <OS> GetAll()
{
ObservableCollection <OS> oses = new ObservableCollection <OS>();
DataTable osTable = DatabaseUtility.GetTable("SELECT * FROM BookOS");
foreach (DataRow row in osTable.Rows)
{
oses.Add(new OS()
{
Id = row.Field <int>("Id"),
NameOS = row.Field <string>("NameOS")
});
}
return(oses);
}
public ObservableCollection <CPU> GetAll()
{
ObservableCollection <CPU> cpus = new ObservableCollection <CPU>();
DataTable cpuTable = DatabaseUtility.GetTable("SELECT * FROM BookCPU");
foreach (DataRow row in cpuTable.Rows)
{
cpus.Add(new CPU()
{
Id = row.Field <int>("Id"),
NameCPU = row.Field <string>("NameCPU")
});
}
return(cpus);
}
public ObservableCollection <UPS> GetAll()
{
ObservableCollection <UPS> upses = new ObservableCollection <UPS>();
DataTable upsesTable = DatabaseUtility.GetTable("SELECT * FROM BookUPS");
foreach (DataRow row in upsesTable.Rows)
{
upses.Add(new UPS()
{
Id = row.Field <int>("Id"),
NameUPS = row.Field <string>("NameUPS")
});
}
return(upses);
}
public ObservableCollection <License> GetAll()
{
ObservableCollection <License> licenses = new ObservableCollection <License>();
DataTable licenseTable = DatabaseUtility.GetTable("SELECT * FROM BookLicense");
foreach (DataRow row in licenseTable.Rows)
{
licenses.Add(new License()
{
Id = row.Field <int>("Id"),
LicenseState = row.Field <string>("License")
});
}
return(licenses);
}
public ObservableCollection <Book> GetAll()
{
ObservableCollection <Book> books = new ObservableCollection <Book>();
DataTable bookTable = DatabaseUtility.GetTable("SELECT * FROM BookBooks WHERE Id IN(" + System.Windows.Application.Current.Properties["AccessToBook"].ToString() + ")");
foreach (DataRow row in bookTable.Rows)
{
books.Add(new Book()
{
Id = row.Field <int>("Id"),
NameBook = row.Field <string>("NameBook")
});
}
return(books);
}
public User Get(int id)
{
DataTable userTable = DatabaseUtility.GetTable("SELECT * FROM BookUsers WHERE Id=@Id", new List <SqlParameter>
{
new SqlParameter("@Id", id)
});
return(new User
{
Id = userTable.Rows[0].Field <int>("Id"),
Name = userTable.Rows[0].Field <string>("Name"),
Position = userTable.Rows[0].Field <string>("Position"),
UserName = userTable.Rows[0].Field <string>("UserName"),
AccessToRegion = userTable.Rows[0].Field <string>("AccessToRegion"),
AccessToBook = userTable.Rows[0].Field <string>("AccessToBook"),
});
}
public ObservableCollection <Board> GetAll()
{
ObservableCollection <Board> boards = new ObservableCollection <Board>();
DataTable boardsTable = DatabaseUtility.GetTable("SELECT * FROM BookBoard");
foreach (DataRow row in boardsTable.Rows)
{
boards.Add(new Board()
{
Id = row.Field <int>("Id"),
Motherboard = row.Field <string>("Motherboard"),
YearOut = row.Field <int?>("YearOut")
});
}
return(boards);
}
public ObservableCollection <Region> GetAll()
{
ObservableCollection <Region> regions = new ObservableCollection <Region>();
DataTable regionTable = DatabaseUtility.GetTable("SELECT * FROM BookRegion WHERE Id IN(" + System.Windows.Application.Current.Properties["AccessToRegion"].ToString() + ")");
foreach (DataRow row in regionTable.Rows)
{
regions.Add(new Region()
{
Id = row.Field <int>("Id"),
FinId = row.Field <int>("FinId"),
NameFD = row.Field <string>("NameFD"),
NameCity = row.Field <string>("NameCity")
});
}
return(regions);
}
public ObservableCollection <Printer> GetAll()
{
ObservableCollection <Printer> printers = new ObservableCollection <Printer>();
DataTable printerTable = DatabaseUtility.GetTable("SELECT * FROM BookPrinter");
foreach (DataRow row in printerTable.Rows)
{
printers.Add(new Printer()
{
Id = row.Field <int>("Id"),
NamePrinter = row.Field <string>("NamePrinter"),
MFU = row.Field <bool>("MFU"),
LaserJet = row.Field <bool>("LaserJet"),
MonoColor = row.Field <bool>("MonoColor")
});
}
return(printers);
}
public ObservableCollection <User> GetAll()
{
ObservableCollection <User> users = new ObservableCollection <User>();
DataTable usersTable = DatabaseUtility.GetTable("SELECT * FROM BookUsers");
foreach (DataRow row in usersTable.Rows)
{
users.Add(new User()
{
Id = row.Field <int>("Id"),
Name = row.Field <string>("Name"),
Position = row.Field <string>("Position"),
UserName = row.Field <string>("UserName"),
AccessToRegion = row.Field <string>("AccessToRegion"),
AccessToBook = row.Field <string>("AccessToBook")
});
}
return(users);
}
public ObservableCollection <VTPrinter> GetAll(int regionId)
{
ObservableCollection <VTPrinter> vtPrinters = new ObservableCollection <VTPrinter>();
DataTable vtPrintersTable = (regionId != 25) ? DatabaseUtility.GetTable("SELECT * FROM v_GetPrinters WHERE OwnerRegion IN (" + regionId + ")") :
DatabaseUtility.GetTable("SELECT * FROM v_GetPrinters");
foreach (DataRow row in vtPrintersTable.Rows)
{
VTPrinter vtPrinter = new VTPrinter()
{
Id = row.Field <int>("Id"),
PrinterId = row.Field <int>("PrinterId"),
Printer = new Printer()
{
Id = row.Field <int>("PrinterId"),
NamePrinter = row.Field <string>("NamePrinter"),
MonoColor = row.Field <bool>("MonoColor"),
MFU = row.Field <bool>("MFU"),
LaserJet = row.Field <bool>("LaserJet")
},
// ниже общие поля для всех моделей VT
YearUsingSince = row.Field <int>("YearUsingSince"),
InventoryNo = row.Field <string>("InventoryNo"),
SerialNumber = row.Field <string>("SerialNumber"),
Ip = row.Field <string>("Ip"),
Room = row.Field <string>("Room"),
Note = row.Field <string>("Note"),
OwnerRegion = row.Field <int>("OwnerRegion"),
InUse = row.Field <bool>("InUse")
};
vtPrinters.Add(vtPrinter);
}
return(vtPrinters);
}
private void AccessEditWindow_OnLoaded(object sender, RoutedEventArgs e)
{
var gridView = new GridView();
gridView.Columns.Add(new GridViewColumn
{
Header = "Ид", DisplayMemberBinding = new Binding()
{
Path = new PropertyPath("Id")
}
});
switch (WorkWith)
{
case WorkWithVariants.Region: // Выбор данных для региона
{
gridView.Columns.Add(new GridViewColumn
{
Header = "Город", DisplayMemberBinding = new Binding()
{
Path = new PropertyPath("NameCity")
}
});
string[] regionsAccessArray = AccessString.Split(new char[] { ',' });
foreach (var region in DB.Regions.GetAll())
{
AccessCollection.Add(new AccessData()
{
Id = region.Id,
NameCity = region.NameCity,
Access = regionsAccessArray.Any(r => r == region.Id.ToString())
});
}
AccessEditView.ItemsSource = AccessCollection;
break;
}
case WorkWithVariants.Book: // Выбор данных для справочника
{
gridView.Columns.Add(new GridViewColumn
{
Header = "Справочник", DisplayMemberBinding = new Binding()
{
Path = new PropertyPath("NameBook")
}
});
string[] bookAccessArray = AccessString.Split(',');
ObservableCollection <Book> books = new ObservableCollection <Book>();
DataTable bookTable = DatabaseUtility.GetTable("SELECT * FROM BookBooks");
foreach (DataRow row in bookTable.Rows)
{
books.Add(new Book()
{
Id = row.Field <int>("Id"),
NameBook = row.Field <string>("NameBook")
});
}
foreach (var book in books)
{
AccessCollection.Add(new AccessData()
{
Id = book.Id,
NameBook = book.NameBook,
Access = bookAccessArray.Any(r => r == book.Id.ToString())
});
}
AccessEditView.ItemsSource = AccessCollection;
break;
}
}
DataTemplate dataTemplate = new DataTemplate();
FrameworkElementFactory elementFactory = new FrameworkElementFactory(typeof(CheckBox));
elementFactory.SetBinding(CheckBox.IsCheckedProperty, new Binding()
{
Path = new PropertyPath("Access"), UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged
});
dataTemplate.VisualTree = elementFactory;
gridView.Columns.Add(new GridViewColumn {
Header = "Доступ", CellTemplate = dataTemplate
});
AccessEditView.View = gridView;
}
private void LoginButton_OnClick(object sender, RoutedEventArgs e)
{
User user = new User()
{
UserName = this.UsernameTextBox.Text,
UserPassword = this.UserPasswordBox.Password
};
//SqlConnection connection = new ServerConnection().ConnectToServer(user);
try
{
string ConnectionString =
$"Data Source = {Properties.Settings.Default.ServerHostname}\\{Properties.Settings.Default.SQLInstance};" +
$" Persist Security Info = True; " +
$"Password = {user.UserPassword};" +
$" User ID = {user.UserName};" +
$" Initial Catalog = {Properties.Settings.Default.Database}";
DatabaseUtility.OpenConnection(ConnectionString);
if (System.Windows.Application.Current.Properties["ConnectionString"] != null)
{
System.Windows.Application.Current.Properties.Remove("ConnectionString");
}
System.Windows.Application.Current.Properties.Add("ConnectionString", ConnectionString);
//Получаем из БД информацию о пользователе
var extUserDataTable = DatabaseUtility.GetTable("SELECT TOP 1 * FROM dbo.fn_t_GetExtendedUserData()");
if (extUserDataTable.Rows.Count == 0)
{
Exception ex = new Exception("У пользователя нет доступа к данным ни одного района");
throw ex;
}
MainWindow main = new MainWindow();
main.CurrentUser = user;
main.CurrentUser.Id = extUserDataTable.Rows[0].Field <int>("Id");
main.CurrentUser.AccessToRegion = extUserDataTable.Rows[0].Field <string>("AccessToRegion");
main.CurrentUser.AccessToBook = extUserDataTable.Rows[0].Field <string>("AccessToBook");
main.CurrentUser.Name = extUserDataTable.Rows[0].Field <string>("Name");
System.Windows.Application.Current.Properties.Add("CurrentUserId", main.CurrentUser.Id);
this.Close();
main.Show();
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 18456:
{
MessageBox.Show("Неверное имя пользователя или пароль");
break;
}
case -1:
{
MessageBox.Show("Не удалось найти сервер MSSQL, указанный в настройках\nПроверьте сеть и файл app.config ");
break;
}
case 4060:
{
MessageBox.Show("Не удалось найти БД, указанную в настройках\nПроверьте файл app.config ");
break;
}
default:
{
throw ex;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
