protected void Page_Load(object sender, EventArgs e)
{
assetsConn = new OleDbConnection(assetsConnParam);
usersConn = new OleDbConnection(usersConnParam);
try
{
usersConn.Open();
usersCmd.Connection = usersConn;
usersCmd.CommandText = "SELECT Permission FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
currentUserLabel.Text = "Zalogowany jako: " + HttpContext.Current.User.Identity.Name;
perm = (string)usersCmd.ExecuteScalar();
usersCmd.Parameters.Clear();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
usersConn.Close();
}
if (!this.Page.User.Identity.IsAuthenticated || perm == "user")
{
FormsAuthentication.RedirectToLoginPage();
}
}
protected void updateButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
string userID = String.Empty;
assetsConn.Open();
usersConn.Open();
usersCmd.Connection = usersConn;
usersCmd.CommandText = "UPDATE Users SET Login = @login, [Password] = @password, Permission = @permission WHERE ID = @id";
usersCmd.Parameters.AddWithValue("@login", LoginBox.Text);
usersCmd.Parameters.AddWithValue("@password", Scripts.SecurePasswordHasher.Hash(PasswordBox.Text));
usersCmd.Parameters.AddWithValue("@permission", PermissionList.SelectedItem.Text);
usersCmd.Parameters.AddWithValue("@id", IDBox.Text);
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "UPDATE People SET Nazwa = @login WHERE ID = @id";
assetsCmd.Parameters.AddWithValue("@login", LoginBox.Text);
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
usersCmd.ExecuteNonQuery();
assetsCmd.ExecuteNonQuery();
usersConn.Close();
assetsConn.Close();
usersCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: aktualizacja danych o użytkowniku z ID: " + IDBox.Text + " w tabelach Users i People");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void addButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
string userID = String.Empty;
assetsConn.Open();
usersConn.Open();
usersCmd.Connection = usersConn;
usersCmd.CommandText = "INSERT INTO Users (Login, [Password], Permission) VALUES (@login, @password, @permission)";
usersCmd.Parameters.AddWithValue("@login", LoginBox.Text);
usersCmd.Parameters.AddWithValue("@password", Scripts.SecurePasswordHasher.Hash(PasswordBox.Text));
usersCmd.Parameters.AddWithValue("@permission", PermissionList.SelectedItem.Text);
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "INSERT INTO People (Nazwa) VALUES (@login)";
assetsCmd.Parameters.AddWithValue("@login", LoginBox.Text);
usersCmd.ExecuteNonQuery();
assetsCmd.ExecuteNonQuery();
usersConn.Close();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: dodanie użytkownika z ID: " + IDBox.Text + " do tabel People i Users");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void removeButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
string userID = String.Empty;
assetsConn.Open();
usersConn.Open();
usersCmd.Connection = usersConn;
usersCmd.CommandText = "DELETE FROM Users WHERE ID = @id";
usersCmd.Parameters.AddWithValue("@id", IDBox.Text);
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "DELETE FROM People WHERE ID = @id";
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
usersCmd.ExecuteNonQuery();
assetsCmd.ExecuteNonQuery();
usersConn.Close();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: usunięcie użytkownika z ID: " + IDBox.Text + " z tabel Users i People");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
private void ShowDataGrid()
{
if (perm == "user")
{
try
{
List <string> columnNames = new List <string>();
string tempAdder = String.Empty;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Close();
assetsConn.Close();
for (int i = 0; i < columnNames.Count; i++)
{
if (i != 2 && i != 3 && i < columnNames.Count - 1)
{
tempAdder += columnNames[i] + ", ";
}
else if (i != 2 && i != 3 && i == columnNames.Count - 1)
{
tempAdder += columnNames[i];
}
}
OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT " + tempAdder + " FROM Assets WHERE Wycofany = 'Nie' AND Skasowany = 'Nie' AND Wypożyczony = 'Nie'", assetsConnParam);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
DataTable dataTable = new DataTable();
dAdapter.Fill(dataTable);
borrowsGrid.DataSource = dataTable;
borrowsGrid.DataBind();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
}
}
protected void loginButton_Click(object sender, EventArgs e)
{
try
{
string userID = String.Empty;
usersConn.Open();
usersCmd.Connection = usersConn;
usersCmd.CommandText = "SELECT [Password] FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", loginBox.Text);
string hashedPass = (string)usersCmd.ExecuteScalar();
usersCmd.Parameters.Clear();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", loginBox.Text);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
if (!Scripts.SecurePasswordHasher.Verify(passwordBox.Text, hashedPass))
{
error.ForeColor = System.Drawing.Color.Red;
error.Text = "Niewłaściwe dane logowania!";
}
else
{
FormsAuthentication.RedirectFromLoginPage(loginBox.Text, false);
loggedInLabel.Text = "Zalogowany jako: " + HttpContext.Current.User.Identity.Name;
}
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: zalogowanie.");
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
error.ForeColor = System.Drawing.Color.Red;
error.Text = exc.Message;
}
finally
{
usersConn.Close();
}
}
protected void removeColumnButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
string userID = String.Empty;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "ALTER TABLE Assets DROP COLUMN " + columnBox.Text;
assetsCmd.ExecuteNonQuery();
assetsCmd.CommandText = "ALTER TABLE Borrows DROP COLUMN " + columnBox.Text;
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: usunięcie pola opisowego: " + columnBox.Text + " z tabeli Assets");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
private void ShowDataGrid(string itemStatus)
{
if (perm == "user")
{
try
{
string userID = String.Empty;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT ID FROM People WHERE Nazwa = @userName";
assetsCmd.Parameters.AddWithValue("@userName", HttpContext.Current.User.Identity.Name);
userID = assetsCmd.ExecuteScalar().ToString();
assetsConn.Close();
assetsCmd.Parameters.Clear();
OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM Borrows WHERE ID_Użytkownika = " + userID + " AND " + itemStatus + " = 'Tak'", assetsConnParam);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
DataTable dataTable = new DataTable();
dAdapter.Fill(dataTable);
returnsGrid.DataSource = dataTable;
returnsGrid.DataBind();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
}
}
private void ShowDataGrid()
{
if (perm == "admin")
{
try
{
OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM People", assetsConnParam);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
DataTable dataTable = new DataTable();
dAdapter.Fill(dataTable);
peopleGrid.DataSource = dataTable;
peopleGrid.DataBind();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
}
}
protected void updateButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
List <string> columnNames = new List <string>();
String[] values;
string tempAdder = String.Empty;
string userID = String.Empty;
int counter = 2;
int maxCounter = 0;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM People";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
values = DescriptionBox.Text.Split(';');
maxCounter = columnNames.Count;
dr.Close();
assetsConn.Close();
for (int i = 2; i < maxCounter; i++)
{
if (i == maxCounter - 1)
{
tempAdder += columnNames[i] + " = @v" + (i - 1) + " ";
}
else
{
tempAdder += columnNames[i] + " = @v" + (i - 1) + ", ";
}
}
assetsConn.Open();
if (UserNameBox.Text == String.Empty && DescriptionBox.Text != String.Empty)
{
assetsCmd.CommandText = "UPDATE People SET " + tempAdder + "WHERE ID = @id";
}
else
{
assetsCmd.CommandText = "UPDATE People SET " + columnNames[1] + " = @v0, " + tempAdder + "WHERE ID = @id";
}
assetsCmd.Parameters.AddWithValue("@v0", UserNameBox.Text);
for (int i = 1; i < (maxCounter - 1); i++)
{
string v = "@v" + i;
if (counter < maxCounter && values.Length == columnNames.Count - 2)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 2]);
counter++;
}
else if (counter < maxCounter && values.Length < columnNames.Count - 2)
{
if (counter - 2 < values.Length)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 2]);
}
else
{
assetsCmd.Parameters.AddWithValue(v, String.Empty);
}
counter++;
}
else if (counter < maxCounter && values.Length > columnNames.Count - 2)
{
if (counter - 2 < columnNames.Count - 2)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 2]);
}
counter++;
}
}
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: aktualizacja danych o użytkowniku z ID: " + IDBox.Text + " w tabeli People");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void removeButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
List <string> columnNames = new List <string>();
string userID = String.Empty;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Close();
assetsConn.Close();
assetsConn.Open();
assetsCmd.Connection = assetsConn;
if (IDBox.Text != String.Empty && ItemNameBox.Text == String.Empty)
{
assetsCmd.CommandText = "DELETE FROM Assets WHERE ID = @id";
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
}
else if (IDBox.Text == String.Empty && ItemNameBox.Text != String.Empty)
{
assetsCmd.CommandText = "DELETE FROM Assets WHERE Nazwa = @itemName";
assetsCmd.Parameters.AddWithValue("@itemName", ItemNameBox.Text);
}
else if (IDBox.Text == String.Empty && ItemNameBox.Text == String.Empty)
{
assetsCmd.CommandText = "DELETE FROM Assets WHERE ID = @id AND Nazwa = @itemName";
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
assetsCmd.Parameters.AddWithValue("@itemName", ItemNameBox.Text);
}
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: usunięcie przedmiotu o ID: " + IDBox.Text + " z tabeli Assets");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void addButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
List <string> columnNames = new List <string>();
String[] values;
string userID = String.Empty;
string tempAdderC = String.Empty;
string tempAdderV = String.Empty;
string cancelled = String.Empty;
string removed = String.Empty;
int counter = 5;
int maxCounter = 0;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
values = DescriptionBox.Text.Split(';');
maxCounter = columnNames.Count;
dr.Close();
assetsConn.Close();
if (columnNames.Count <= 19)
{
for (int i = 5; i < maxCounter; i++)
{
tempAdderC += columnNames[i] + ", ";
tempAdderV += "@v" + (i - 3) + ", ";
}
if (cancelledCheckBox.Checked)
{
cancelled = "Tak";
}
else
{
cancelled = "Nie";
}
if (removedCheckBox.Checked)
{
removed = "Tak";
}
else
{
removed = "Nie";
}
assetsConn.Open();
if (DescriptionBox.Text == String.Empty)
{
assetsCmd.CommandText = "INSERT INTO Assets (Nazwa, Wycofany, Skasowany) VALUES (@itemName, @cancelled, @removed)";
assetsCmd.Parameters.AddWithValue("@itemName", ItemNameBox.Text);
}
else
{
assetsCmd.CommandText = "INSERT INTO Assets (" + columnNames[1] + ", " + tempAdderC + "Wycofany, Skasowany) VALUES (@itemName, " + tempAdderV + "@cancelled, @removed)";
assetsCmd.Parameters.AddWithValue("@itemName", ItemNameBox.Text);
for (int i = 1; i < (maxCounter - 1); i++)
{
string v = "@v" + i;
if (counter < maxCounter && values.Length == columnNames.Count - 5)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
counter++;
}
else if (counter < maxCounter && values.Length < columnNames.Count - 5)
{
if (counter - 5 < values.Length)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
}
else
{
assetsCmd.Parameters.AddWithValue(v, String.Empty);
}
counter++;
}
else if (counter < maxCounter && values.Length > columnNames.Count - 5)
{
if (counter - 5 < columnNames.Count - 4)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
}
counter++;
}
}
}
assetsCmd.Parameters.AddWithValue("@cancelled", cancelled);
assetsCmd.Parameters.AddWithValue("@removed", removed);
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: dodanie przedmiotu o ID: " + IDBox.Text + " do tabeli Assets");
}
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void addColumnButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
List <string> columnNames = new List <string>();
string tempAdder = String.Empty;
string userID = String.Empty;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM People";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Close();
assetsConn.Close();
if (columnNames.Count <= 12)
{
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "ALTER TABLE People ADD " + columnBox.Text + " string";
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
}
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: dodanie pola opisowego: " + columnBox.Text + " do tabeli People");
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void updateFromFileButton_Click(object sender, EventArgs e)
{
if (perm == "admin")
{
try
{
if (descriptionFileUpload.HasFile)
{
string filePath = AppDomain.CurrentDomain.BaseDirectory + "Uploads\\" + descriptionFileUpload.FileName;
descriptionFileUpload.SaveAs(filePath);
StreamReader sr = new StreamReader(filePath, Encoding.Default, true);
string line = sr.ReadLine();
string[] values = line.Split('\t');
List <string> columnNames = new List <string>();
string userID = String.Empty;
string tempAdder = String.Empty;
string cancelled = String.Empty;
string removed = String.Empty;
int counter = 5;
int maxCounter = 0;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
maxCounter = columnNames.Count;
dr.Close();
assetsConn.Close();
for (int i = 5; i < maxCounter; i++)
{
tempAdder += columnNames[i] + " = @v" + (i - 1) + ", ";
}
if (cancelledCheckBox.Checked)
{
cancelled = "Tak";
}
else
{
cancelled = "Nie";
}
if (removedCheckBox.Checked)
{
removed = "Tak";
}
else
{
removed = "Nie";
}
assetsConn.Open();
if (ItemNameBox.Text == String.Empty && DescriptionBox.Text != String.Empty)
{
assetsCmd.CommandText = "UPDATE Assets SET " + tempAdder + "Wycofany = @cancelled, Skasowany = @removed WHERE ID = @id";
}
else
{
assetsCmd.CommandText = "UPDATE Assets SET " + columnNames[1] + " = @v0, " + tempAdder + "Wycofany = @cancelled, Skasowany = @removed WHERE ID = @id";
}
assetsCmd.Parameters.AddWithValue("@v0", ItemNameBox.Text);
for (int i = 1; i < (maxCounter - 1); i++)
{
string v = "@v" + i;
if (counter < maxCounter && values.Length == columnNames.Count - 5)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
counter++;
}
else if (counter < maxCounter && values.Length < columnNames.Count - 5)
{
if (counter - 5 < values.Length)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
}
else
{
assetsCmd.Parameters.AddWithValue(v, String.Empty);
}
counter++;
}
else if (counter < maxCounter && values.Length > columnNames.Count - 5)
{
if (counter - 5 < columnNames.Count - 5)
{
assetsCmd.Parameters.AddWithValue(v, values[counter - 5]);
}
counter++;
}
}
assetsCmd.Parameters.AddWithValue("@cancelled", cancelled);
assetsCmd.Parameters.AddWithValue("@removed", removed);
assetsCmd.Parameters.AddWithValue("@id", IDBox.Text);
assetsCmd.ExecuteNonQuery();
assetsConn.Close();
assetsCmd.Parameters.Clear();
usersConn.Open();
usersCmd.CommandText = "SELECT ID FROM Users WHERE Login = @login";
usersCmd.Parameters.AddWithValue("@login", HttpContext.Current.User.Identity.Name);
userID = usersCmd.ExecuteScalar().ToString();
usersCmd.Parameters.Clear();
usersConn.Close();
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: aktualizacja danych o przedmiocie z ID: " + IDBox.Text + " w tabeli Assets");
ClearTextBoxes();
}
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void returnButton_Click(object sender, EventArgs e)
{
if (perm == "user")
{
try
{
List <string> columnNames = new List <string>();
List <string> values = new List <string>();
string userID = String.Empty;
string itemID = String.Empty;
string date = String.Empty;
string borrowDateString = String.Empty;
DateTime dateNow, borrowDate;
itemID = returnsGrid.SelectedRow.Cells[2].Text;
borrowDateString = returnsGrid.SelectedRow.Cells[6].Text;
date = System.DateTime.Now.ToString();
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
dateInfo.FullDateTimePattern = "dd/MM/yyyy HH:mm:ss tt";
dateNow = Convert.ToDateTime(date, dateInfo);
borrowDate = Convert.ToDateTime(borrowDateString, dateInfo);
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT ID FROM People WHERE Nazwa = @userName";
assetsCmd.Parameters.AddWithValue("@userName", HttpContext.Current.User.Identity.Name);
userID = assetsCmd.ExecuteScalar().ToString();
assetsCmd.Parameters.Clear();
assetsCmd.CommandText = "UPDATE Borrows SET Wypożyczony = 'Nie', Zwrócony = 'Tak', Data_zwrócenia = @dateNow WHERE ID_przedmiotu = @itemID AND ID_użytkownika = @userID AND Data_wypożyczenia = @borrowDate";
assetsCmd.Parameters.AddWithValue("@dateNow", dateNow);
assetsCmd.Parameters.AddWithValue("@itemID", itemID);
assetsCmd.Parameters.AddWithValue("@userID", userID);
assetsCmd.Parameters.AddWithValue("@borrowDate", borrowDate);
assetsCmd.ExecuteNonQuery();
assetsCmd.Parameters.Clear();
assetsCmd.CommandText = "UPDATE Assets SET Wypożyczony = 'Nie' WHERE ID = @itemID";
assetsCmd.Parameters.AddWithValue("@itemID", itemID);
assetsCmd.ExecuteNonQuery();
assetsCmd.Parameters.Clear();
assetsConn.Close();
if (createPDFCheckBox.Checked)
{
assetsConn.Open();
assetsCmd.CommandText = "SELECT * FROM Borrows WHERE ID = " + itemID;
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
values.Add(dr.GetValue(i).ToString());
}
assetsConn.Close();
var doc = new Document();
PdfWriter.GetInstance(doc, new FileStream(AppDomain.CurrentDomain.BaseDirectory + "PDF\\Zwroty\\" + System.DateTime.Now.ToString("yyyy_MM_dd-HH_mm") + ".pdf", FileMode.Create));
doc.Open();
Font titleFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 14f, Font.BOLD);
Font dataFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 12f);
Phrase title = new Phrase("Potwierdzenie zwrotu", titleFont);
Paragraph titlePar = new Paragraph(title);
Paragraph dataPar = new Paragraph();
for (int i = 0; i < columnNames.Count; i++)
{
Phrase p = new Phrase(columnNames[i] + ": " + values[i] + "\n", dataFont);
dataPar.Add(p);
}
titlePar.SetAlignment("Center");
doc.Add(titlePar);
doc.Add(dataPar);
doc.Close();
Scripts.OperationLogCreator op1 = new Scripts.OperationLogCreator();
op1.OperationLog("UserID: " + userID + " || Operacja wykonana: wygenerowanie wydruku potwierdzającego zwrot przedmiotu o ID: " + itemID);
}
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: zwrot przedmiotu o ID: " + itemID);
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void searchButton_Click(object sender, EventArgs e)
{
if (perm == "user")
{
try
{
if (searchValueBox.Text.Length < 3 && !searchValueBox.Text.Contains('*'))
{
errorLabel.ForeColor = System.Drawing.Color.Red;
errorLabel.Text = "Za mało znaków wyszukiwania!";
}
else if (searchValueBox.Text.Length >= 3 || searchValueBox.Text.Contains('*'))
{
List <string> columnNames = new List <string>();
string tempAdder = String.Empty;
string searchValue = String.Empty;
searchValue = searchValueBox.Text.Replace('*', '%');
searchValue = searchValue.Replace('?', '_');
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Close();
assetsConn.Close();
for (int i = 0; i < columnNames.Count; i++)
{
if (i != 2 && i != 3 && i < columnNames.Count - 1)
{
tempAdder += columnNames[i] + ", ";
}
else if (i != 2 && i != 3 && i == columnNames.Count - 1)
{
tempAdder += columnNames[i];
}
}
assetsCmd.Connection = assetsConn;
OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT " + tempAdder + " FROM Assets WHERE " + columnNameBox.Text + " LIKE '" + searchValue + "' AND Wycofany = 'Nie' AND Skasowany = 'Nie' ORDER BY ID", assetsConnParam);
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
DataTable dataTable = new DataTable();
dAdapter.Fill(dataTable);
borrowsGrid.DataSource = dataTable;
borrowsGrid.DataBind();
ClearTextBoxes();
}
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}
protected void allHistoryButton_Click(object sender, EventArgs e)
{
try
{
List <string> columnNames = new List <string>();
List <string> values = new List <string>();
string userID = String.Empty;
string date = System.DateTime.Now.ToString();
string fDate, tDate;
DateTime fromDate, toDate;
int j = 0;
fDate = fromDateTextBox.Text;
tDate = toDateTextBox.Text;
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
dateInfo.FullDateTimePattern = "dd/MM/yyyy HH:mm:ss tt";
fromDate = Convert.ToDateTime(fDate, dateInfo);
toDate = Convert.ToDateTime(tDate, dateInfo);
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Borrows WHERE Data_wypożyczenia BETWEEN @fromDate AND @toDate ORDER BY @sortColumn";
assetsCmd.Parameters.AddWithValue("@fromDate", fromDate);
assetsCmd.Parameters.AddWithValue("@toDate", toDate);
if (sortColumnBox.Text == String.Empty)
{
assetsCmd.Parameters.AddWithValue("@sortColumn", "ID");
}
else
{
assetsCmd.Parameters.AddWithValue("@sortColumn", sortColumnBox.Text);
}
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
if (dr.HasRows)
{
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
values.Add(dr.GetValue(i).ToString());
}
}
}
assetsConn.Close();
assetsCmd.Parameters.Clear();
var doc = new Document();
PdfWriter.GetInstance(doc, new FileStream(AppDomain.CurrentDomain.BaseDirectory + "PDF\\Raporty\\" + System.DateTime.Now.ToString("yyyy_MM_dd-HH_mm") + ".pdf", FileMode.Create));
doc.Open();
Font titleFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 14f, Font.BOLD);
Font dataFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 12f);
Phrase title = new Phrase("Raport wypożyczeń na dzień " + date, titleFont);
Paragraph titlePar = new Paragraph(title);
Paragraph dataPar = new Paragraph();
for (int i = 0; i < columnNames.Count; i++)
{
if (i == columnNames.Count - 1)
{
Phrase p = new Phrase(columnNames[i] + ": " + values[j] + "\n\n", dataFont);
dataPar.Add(p);
i = -1;
}
else
{
Phrase p = new Phrase(columnNames[i] + ": " + values[j] + "\n", dataFont);
dataPar.Add(p);
}
j++;
if (j == values.Count)
{
break;
}
}
titlePar.SetAlignment("Center");
doc.Add(titlePar);
doc.Add(dataPar);
doc.Close();
Scripts.OperationLogCreator op1 = new Scripts.OperationLogCreator();
op1.OperationLog("UserID: " + userID + " || Operacja wykonana: wygenerowanie historii operacji na sprzęcie");
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
protected void oneHistoryButton_Click(object sender, EventArgs e)
{
try
{
List <string> columnNames = new List <string>();
List <string> values = new List <string>();
string userID = String.Empty;
string date = System.DateTime.Now.ToString();
int j = 0;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Borrows WHERE ID_przedmiotu = @itemID";
assetsCmd.Parameters.AddWithValue("@itemID", IDBox.Text);
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
if (dr.HasRows)
{
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
values.Add(dr.GetValue(i).ToString());
}
}
}
assetsConn.Close();
assetsCmd.Parameters.Clear();
var doc = new Document();
PdfWriter.GetInstance(doc, new FileStream(AppDomain.CurrentDomain.BaseDirectory + "PDF\\Raporty\\" + System.DateTime.Now.ToString("yyyy_MM_dd-HH_mm") + ".pdf", FileMode.Create));
doc.Open();
Font titleFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 14f, Font.BOLD);
Font dataFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 12f);
Phrase title = new Phrase("Historia operacji dla sprzętu o ID: " + IDBox.Text, titleFont);
Paragraph titlePar = new Paragraph(title);
Paragraph dataPar = new Paragraph();
for (int i = 0; i < columnNames.Count; i++)
{
if (i == columnNames.Count - 1)
{
Phrase p = new Phrase(columnNames[i] + ": " + values[j] + "\n\n", dataFont);
dataPar.Add(p);
i = -1;
}
else
{
Phrase p = new Phrase(columnNames[i] + ": " + values[j] + "\n", dataFont);
dataPar.Add(p);
}
j++;
if (j == values.Count)
{
break;
}
}
titlePar.SetAlignment("Center");
doc.Add(titlePar);
doc.Add(dataPar);
doc.Close();
Scripts.OperationLogCreator op1 = new Scripts.OperationLogCreator();
op1.OperationLog("UserID: " + userID + " || Operacja wykonana: wygenerowanie historii operacji dla sprzętu o ID: " + IDBox.Text);
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
protected void borrowButton_Click(object sender, EventArgs e)
{
if (perm == "user")
{
try
{
List <string> columnNames = new List <string>();
List <string> values = new List <string>();
string itemID = String.Empty;
string userID = String.Empty;
string date = String.Empty;
string itemColumns = String.Empty;
string userColumns = String.Empty;
string itemValues = String.Empty;
string userValues = String.Empty;
string addColumns = String.Empty;
DateTime dateNow;
assetsConn.Open();
assetsCmd.Connection = assetsConn;
assetsCmd.CommandText = "SELECT * FROM Assets";
OleDbDataReader dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 4)
{
itemColumns += dr.GetName(i) + ", ";
}
}
dr.Close();
assetsCmd.CommandText = "SELECT * FROM People";
dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 1 && i != dr.FieldCount - 1)
{
userColumns += dr.GetName(i) + ", ";
}
if (i > 1 && i == dr.FieldCount - 1)
{
userColumns += dr.GetName(i);
}
}
dr.Close();
itemID = borrowsGrid.SelectedRow.Cells[1].Text;
assetsCmd.CommandText = "SELECT ID FROM People WHERE Nazwa = @userName";
assetsCmd.Parameters.AddWithValue("@userName", HttpContext.Current.User.Identity.Name);
userID = assetsCmd.ExecuteScalar().ToString();
date = System.DateTime.Now.ToString();
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
dateInfo.FullDateTimePattern = "dd/MM/yyyy HH:mm:ss tt";
dateNow = Convert.ToDateTime(date, dateInfo);
assetsCmd.Parameters.Clear();
itemColumns = itemColumns.Remove(itemColumns.LastIndexOf(','));
assetsCmd.CommandText = "SELECT " + itemColumns + " FROM Assets WHERE ID = " + itemID;
dr = assetsCmd.ExecuteReader();
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
itemValues += "'" + dr.GetString(i) + "'" + ", ";
}
dr.Close();
assetsCmd.CommandText = "SELECT " + userColumns + " FROM People WHERE ID = " + userID;
dr = assetsCmd.ExecuteReader();
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
if (i < dr.FieldCount - 1)
{
userValues += "'" + dr.GetString(i) + "'" + ", ";
}
if (i == dr.FieldCount - 1)
{
userValues += "'" + dr.GetString(i) + "'";
}
}
dr.Close();
itemColumns += ", ";
assetsCmd.CommandText = "SELECT * FROM Assets";
dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 3)
{
addColumns += dr.GetName(i) + " string" + ", ";
}
}
dr.Close();
assetsCmd.CommandText = "SELECT * FROM People";
dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
if (i > 1 && i < dr.FieldCount - 1)
{
addColumns += dr.GetName(i) + " string" + ", ";
}
if (i == dr.FieldCount - 1)
{
addColumns += dr.GetName(i) + " string";
}
}
dr.Close();
if (columnNames.Count == 7)
{
assetsCmd.CommandText = "ALTER TABLE Borrows ADD " + addColumns;
assetsCmd.ExecuteNonQuery();
}
assetsCmd.CommandText = "INSERT INTO Borrows (ID_przedmiotu, ID_użytkownika, Wypożyczony, Data_wypożyczenia, " + itemColumns + userColumns + ") VALUES (@itemID, @userID, 'Tak', @dateNow, " + itemValues + userValues + ")";
assetsCmd.Parameters.AddWithValue("@itemID", itemID);
assetsCmd.Parameters.AddWithValue("@userID", userID);
assetsCmd.Parameters.AddWithValue("@dateNow", dateNow);
assetsCmd.ExecuteNonQuery();
assetsCmd.Parameters.Clear();
assetsCmd.CommandText = "UPDATE Assets SET Wypożyczony = 'Tak' WHERE ID = @itemID";
assetsCmd.Parameters.AddWithValue("@itemID", itemID);
assetsCmd.ExecuteNonQuery();
assetsCmd.Parameters.Clear();
assetsConn.Close();
if (createPDFCheckBox.Checked)
{
assetsConn.Open();
assetsCmd.CommandText = "SELECT * FROM Borrows WHERE ID = (SELECT MAX(ID) FROM Borrows)";
dr = assetsCmd.ExecuteReader();
for (int i = 0; i < dr.FieldCount; i++)
{
columnNames.Add(dr.GetName(i));
}
dr.Read();
for (int i = 0; i < dr.FieldCount; i++)
{
values.Add(dr.GetValue(i).ToString());
}
assetsConn.Close();
var doc = new Document();
PdfWriter.GetInstance(doc, new FileStream(AppDomain.CurrentDomain.BaseDirectory + "PDF\\Wypożyczenia\\" + System.DateTime.Now.ToString("yyyy_MM_dd-HH_mm") + ".pdf", FileMode.Create));
doc.Open();
Font titleFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 14f, Font.BOLD);
Font dataFont = FontFactory.GetFont(BaseFont.TIMES_ROMAN, BaseFont.CP1257, 12f);
Phrase title = new Phrase("Potwierdzenie wypożyczenia", titleFont);
Paragraph titlePar = new Paragraph(title);
Paragraph dataPar = new Paragraph();
for (int i = 0; i < columnNames.Count; i++)
{
Phrase p = new Phrase(columnNames[i] + ": " + values[i] + "\n", dataFont);
dataPar.Add(p);
}
titlePar.SetAlignment("Center");
doc.Add(titlePar);
doc.Add(dataPar);
doc.Close();
Scripts.OperationLogCreator op1 = new Scripts.OperationLogCreator();
op1.OperationLog("UserID: " + userID + " || Operacja wykonana: wygenerowanie wydruku potwierdzającego wypożyczenie przedmiotu o ID: " + itemID);
}
Scripts.OperationLogCreator op = new Scripts.OperationLogCreator();
op.OperationLog("UserID: " + userID + " || Operacja wykonana: wypożyczenie przedmiotu o ID: " + itemID);
ClearTextBoxes();
}
catch (Exception exc)
{
Scripts.ErrorLogCreator err = new Scripts.ErrorLogCreator();
err.ErrorLog(exc.Message);
errorLabel.Text = "Błąd! " + exc.Message;
}
finally
{
assetsConn.Close();
usersConn.Close();
}
}
}