public IEnumerable <StudentTicketBLL> searchBy(StudentTicketBLL student)
{
IEnumerable <StudentTicketDAL> listDal = new List <StudentTicketDAL>();
StudentTicketDAL searchPattern = new StudentTicketDAL
{
lastName = student.lastName,
name = student.name,
patronimic = student.patronimic,
number = student.number,
serial = student.serial,
facult = student.facult,
kurs = student.kurs,
group = student.group,
speciality = student.speciality,
roomNumber = student.roomNumber
};
PGUserRepository repository = repositoryFactory.getUserRepository();
listDal = (repository.searchBy(searchPattern) as IEnumerable <StudentTicketDAL>);
return(listDal.Select(s => new StudentTicketBLL
{
lastName = s.lastName,
name = s.name,
number = s.number,
patronimic = s.patronimic,
serial = s.serial,
facult = s.facult,
group = s.group,
kurs = s.kurs,
speciality = s.speciality,
roomNumber = s.roomNumber
}).AsEnumerable());
}
public StudentTicketBLL searchBySerial(StudentTicketBLL student)
{
StudentTicketDAL stDal = new StudentTicketDAL
{
serial = student.serial,
number = student.number,
lastName = student.lastName,
name = student.name,
patronimic = student.patronimic,
kurs = student.kurs,
facult = student.facult,
group = student.group,
speciality = student.speciality,
roomNumber = student.roomNumber
};
StudentTicketDAL resultStud;
PGUserRepository repository = repositoryFactory.getUserRepository();
resultStud = repository.findBySerial(stDal);
return(new StudentTicketBLL
{
serial = resultStud.serial,
number = resultStud.number,
lastName = resultStud.lastName,
name = resultStud.name,
patronimic = resultStud.patronimic,
kurs = resultStud.kurs,
facult = resultStud.facult,
group = resultStud.group,
speciality = resultStud.speciality,
roomNumber = resultStud.roomNumber
});
}
private static StudentTicketDAL fromReaderToStudent(NpgsqlDataReader reader)
{
var stud = new StudentTicketDAL();
if (reader.HasRows)
{
stud = new StudentTicketDAL
{
lastName = clearFromSpaces(Convert.ToString(reader["u_last_name"])),
name = clearFromSpaces(Convert.ToString(reader["u_name"])),
kurs = Convert.ToInt32(reader["kurs"]),
facult = clearFromSpaces(Convert.ToString(reader["facult"])),
speciality = clearFromSpaces(Convert.ToString(reader["spec"])),
group = Convert.ToInt32(reader["s_group"]),
number = clearFromSpaces(Convert.ToString(reader["u_number"])),
serial = clearFromSpaces(Convert.ToString(reader["u_serial"])),
roomNumber = Convert.ToInt32(reader["room_num"])
};
if (reader["u_patr"] != DBNull.Value)
{
stud.patronimic = clearFromSpaces(Convert.ToString(reader["u_patr"]));
}
}
return(stud);
}
public StudentTicketDAL findBySerial(StudentTicketDAL student)
{
string searchQuery = "SELECT u_serial,u_number,u_last_name,u_name,u_patr, kurs,facult,spec,s_group,room_num FROM stud_view WHERE u_serial=@serial AND u_number=@number";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(searchQuery, conn);
cmd.Parameters.AddWithValue("@serial", student.serial);
cmd.Parameters.AddWithValue("@number", student.number);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
return(fromReaderToStudent(reader));
}
}
}
public void deleteUser(StudentTicketBLL student)
{
PGUserRepository repository = repositoryFactory.getUserRepository();
StudentTicketDAL studentDAL = new StudentTicketDAL
{
serial = student.serial,
number = student.number,
lastName = student.lastName,
name = student.name,
patronimic = student.patronimic,
kurs = student.kurs,
speciality = student.speciality,
facult = student.facult,
group = student.group,
roomNumber = student.roomNumber
};
repository.removeBySerial(studentDAL);
}
public void updateData(StudentTicketBLL updatedStudent)
{
StudentTicketDAL sDal = new StudentTicketDAL
{
name = updatedStudent.name,
lastName = updatedStudent.lastName,
patronimic = updatedStudent.patronimic,
number = updatedStudent.number,
serial = updatedStudent.serial,
kurs = updatedStudent.kurs,
facult = updatedStudent.facult,
group = updatedStudent.group,
speciality = updatedStudent.speciality,
roomNumber = updatedStudent.roomNumber
};
PGUserRepository repository = repositoryFactory.getUserRepository();
repository.updateInfo(sDal);
}
public void addUser(StudentTicketBLL student)
{
StudentTicketDAL studDAL = new StudentTicketDAL()
{
lastName = student.lastName,
name = student.name,
patronimic = student.patronimic,
number = student.number,
serial = student.serial,
facult = student.facult,
kurs = student.kurs,
group = student.group,
speciality = student.speciality,
roomNumber = student.roomNumber
};
PGUserRepository repository = repositoryFactory.getUserRepository();
repository.addUser(studDAL);
}
public void updateInfo(StudentTicketDAL updatedStudent)
{
string updateQuery = "UPDATE students " +
"SET u_last_name=@last_name," +
"u_name=@name, " +
"u_patr=@patr, " +
"kurs=@kurs, " +
"spec=@spec, " +
"facult=@facult, " +
"s_group=@group " +
"WHERE u_serial=@serial AND u_number=@number";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(updateQuery, conn);
cmd.Parameters.AddWithValue("@last_name", updatedStudent.lastName);
cmd.Parameters.AddWithValue("@name", updatedStudent.name);
cmd.Parameters.AddWithValue("@kurs", updatedStudent.kurs);
cmd.Parameters.AddWithValue("@spec", updatedStudent.speciality);
cmd.Parameters.AddWithValue("@facult", updatedStudent.facult);
cmd.Parameters.AddWithValue("@group", updatedStudent.group);
cmd.Parameters.AddWithValue("@serial", updatedStudent.serial);
cmd.Parameters.AddWithValue("@number", updatedStudent.number);
if (updatedStudent.patronimic != null)
{
cmd.Parameters.AddWithValue("@patr", updatedStudent.patronimic);
}
else
{
cmd.Parameters.AddWithValue("@patr", DBNull.Value);
}
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Информация успешно обновлена:", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка обновления записи в БД! Сообщение:" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void removeBySerial(StudentTicketDAL student)
{
string delQuery = "SELECT remove_stud(@u_serial,@u_number)";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(delQuery, conn);
cmd.Parameters.AddWithValue("@u_serial", student.serial);
cmd.Parameters.AddWithValue("@u_number", student.number);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Студент успешно удален из БД", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка удаления записи БД! Сообщение:" + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void resettleStudent(StudentTicketDAL student)
{
string query = "SELECT resettle(@serial,@number,@room)";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
cmd.Parameters.AddWithValue("@serial", student.serial);
cmd.Parameters.AddWithValue("@number", student.number);
cmd.Parameters.AddWithValue("@room", student.roomNumber);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Студент успешно переселен!", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка переселения! Текст ошибки:" + ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void addUser(StudentTicketDAL student)
{
string addQuery = "SELECT settling('С',@last_name,@name,@patr,@kurs,@facult,@spec,@group,@serial,@number,@room)";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(addQuery, conn);
cmd.Parameters.AddWithValue("@serial", student.serial);
cmd.Parameters.AddWithValue("@number", student.number);
cmd.Parameters.AddWithValue("@last_name", student.lastName);
cmd.Parameters.AddWithValue("@name", student.name);
cmd.Parameters.AddWithValue("@kurs", student.kurs);
cmd.Parameters.AddWithValue("@facult", student.facult);
cmd.Parameters.AddWithValue("@spec", student.speciality);
cmd.Parameters.AddWithValue("@group", student.group);
cmd.Parameters.AddWithValue("@room", student.roomNumber);
if (student.patronimic != null)
{
cmd.Parameters.AddWithValue("@patr", student.patronimic);
}
else
{
cmd.Parameters.AddWithValue("@patr", DBNull.Value);
}
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Студент успешно добавлен", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка добавления записи в БД! Сообщение:" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public IEnumerable <StudentTicketDAL> searchBy(StudentTicketDAL student)
{
List <StudentTicketDAL> list = new List <StudentTicketDAL>();
string getQuery = "SELECT u_serial,u_number,u_last_name,u_name,u_patr, kurs,facult,spec,s_group,room_num FROM stud_view ";
//дополнить поиск по вхождению подстрок SIMILAR TO
bool hasPrev = false;
#region LastName
if (!string.IsNullOrWhiteSpace(student.lastName))
{
getQuery += "WHERE u_last_name SIMILAR TO '%" + student.lastName.ToString() + "%' ";
hasPrev = true;
}
#endregion
#region Name
if (!string.IsNullOrWhiteSpace(student.name))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_name SIMILAR TO'%" + student.name.ToString() + "%'";
}
else
{
getQuery += "WHERE u_name SIMILAR TO '%" + student.name.ToString() + "%'";
}
hasPrev = true;
}
#endregion
#region Patr
if (!string.IsNullOrWhiteSpace(student.patronimic))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_patr SIMILAR TO '%" + student.patronimic.ToString() + "%' ";
}
else
{
getQuery += "WHERE u_patr='%" + student.patronimic.ToString() + "%' ";
}
hasPrev = true;
}
#endregion
#region Faculty
if (!string.IsNullOrWhiteSpace(student.facult))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " facult SIMILAR TO '%" + student.facult.ToString() + "%' ";
}
else
{
getQuery += "WHERE facult SIMILAR TO '%" + student.facult.ToString() + "%' ";
}
hasPrev = true;
}
#endregion
#region Kurs
if (student.kurs > 0)
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " kurs='" + student.kurs + "' ";
}
else
{
getQuery += "WHERE kurs='" + student.kurs + "' ";
}
hasPrev = true;
}
#endregion
#region Speciality
if (!string.IsNullOrWhiteSpace(student.speciality))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " spec SIMILAR TO '%" + student.speciality + "%' ";
}
else
{
getQuery += "WHERE spec SIMILAR TO '%" + student.speciality + "%' ";
}
hasPrev = true;
}
#endregion
#region Group
if (student.group > 0)
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " s_group='" + student.group + "' ";
}
else
{
getQuery += "WHERE s_group='" + student.group + "' ";
}
hasPrev = true;
}
#endregion
#region Serial
if (!string.IsNullOrWhiteSpace(student.serial))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_serial='" + student.serial + "' ";
}
else
{
getQuery += "WHERE u_serial='" + student.serial + "' ";
}
hasPrev = true;
}
#endregion
#region Number
if (!string.IsNullOrWhiteSpace(student.number))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_number='" + student.number + "' ";
}
else
{
getQuery += "WHERE u_number='" + student.number + "' ";
}
hasPrev = true;
}
#endregion
#region Room
if (student.roomNumber > 0)
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " room_num=" + student.roomNumber + " ";
}
else
{
getQuery += "WHERE room_num=" + student.roomNumber + " ";
}
hasPrev = true;
}
#endregion
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(getQuery, conn);
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(fromReaderToStudent(reader));
}
}
}
return(list.AsEnumerable());
}
