public IEnumerable <FixJournalBLL> searchBy(FixJournalBLL note)
{
IEnumerable <FixJournalDAL> listDAL = new List <FixJournalDAL>();
FixJournalDAL repDAL = new FixJournalDAL
{
ID = note.ID,
year = note.year,
day = note.day,
month = note.month,
description = note.description,
userTicketSerial = note.userTicketSerial,
userTicketNumber = note.userTicketNumber
};
PGJournalRepository repository = repositoryFactory.getJournalRepository();
listDAL = repository.searchBy(repDAL);
return(listDAL.Select(f => new FixJournalBLL
{
ID = f.ID,
year = f.year,
day = f.day,
month = f.month,
description = f.description,
userTicketSerial = f.userTicketSerial,
userTicketNumber = f.userTicketNumber
}));
}
public void updateNote(FixJournalDAL note)
{
string updateQuery = "UPDATE fixes " +
"SET fixed_finish_date=@finish_date," +
"fixed_report=@info " +
"WHERE fix_req_num=@reqNum AND u_serial=@serial AND u_number=@number";
string date = string.Format("{0}-{1}-{2}", note.year.Value, note.month.Value, note.day.Value);
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(updateQuery, conn);
cmd.Parameters.AddWithValue("@finish_date", Convert.ToDateTime(date));
cmd.Parameters.AddWithValue("@info", note.description);
cmd.Parameters.AddWithValue("@reqNum", note.ID);
cmd.Parameters.AddWithValue("@serial", note.userTicketSerial);
cmd.Parameters.AddWithValue("@number", note.userTicketNumber);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Информация успешно обновлена:", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка обновления записи в БД! Сообщение:\n" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void addNote(FixJournalDAL note)
{
string addQuery = "SELECT addreport(@requestId,@date,@info,@serial,@number)";
string date = string.Format("{0}-{1}-{2}", note.year.Value, note.month.Value, note.day.Value);
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(addQuery, conn);
cmd.Parameters.AddWithValue("@requestId", note.ID);
cmd.Parameters.AddWithValue("@date", date);
cmd.Parameters.AddWithValue("@info", note.description);
cmd.Parameters.AddWithValue("@serial", note.userTicketSerial);
cmd.Parameters.AddWithValue("@number", note.userTicketNumber);
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 updateNote(FixJournalBLL note)
{
FixJournalDAL reportDAL = new FixJournalDAL
{
ID = note.ID,
year = note.year,
day = note.day,
month = note.month,
description = note.description,
userTicketSerial = note.userTicketSerial,
userTicketNumber = note.userTicketNumber
};
PGJournalRepository repository = repositoryFactory.getJournalRepository();
repository.updateNote(reportDAL);
}
private FixJournalDAL fromReaderToFixJournal(NpgsqlDataReader reader)
{
var report = new FixJournalDAL();
if (reader.HasRows)
{
report = new FixJournalDAL
{
ID = Convert.ToInt32(reader["fix_req_num"]),
description = reader["fixed_report"].ToString(),
userTicketSerial = reader["u_serial"].ToString(),
userTicketNumber = reader["u_number"].ToString(),
year = getYearFromReader(reader["fixed_finish_date"].ToString()),
month = getMonthFromString(reader["fixed_finish_date"].ToString()),
day = getDayFromString(reader["fixed_finish_date"].ToString())
};
}
return(report);
}
public void deleteNote(FixJournalDAL note)
{
string addQuery = "DELETE FROM fixes WHERE fix_req_num=@reqNum";
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(addQuery, conn);
cmd.Parameters.AddWithValue("@reqNum", note.ID);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Отчет успешно удален", "Успех", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Ошибка удаления записи из БД! Сообщение:\n" + ex.Message.Substring(6, ex.Message.Length - 6), "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public IEnumerable <FixJournalDAL> searchBy(FixJournalDAL note)
{
List <FixJournalDAL> list = new List <FixJournalDAL>();
string getQuery = "SELECT * FROM fixes ";
bool hasPrev = false;
#region ID
if (note.ID != null)
{
getQuery += "WHERE fix_req_num='" + note.ID.ToString() + "' ";
hasPrev = true;
}
#endregion
#region Year
if (note.year != null)
{
string extractYear = " EXTRACT(YEAR FROM fixed_finish_date)=" + note.year + " ";
if (hasPrev)
{
getQuery += " AND ";
getQuery += extractYear;
}
else
{
getQuery += "WHERE " + extractYear;
}
hasPrev = true;
}
#endregion
#region Month
if (note.month != null)
{
string extractMonth = " EXTRACT(Month FROM fixed_finish_date)=" + note.month + " ";
if (hasPrev)
{
getQuery += " AND ";
getQuery += extractMonth;
}
else
{
getQuery += "WHERE " + extractMonth;
}
hasPrev = true;
}
#endregion
#region Day
if (note.day != null)
{
string extractDay = " EXTRACT(DAY FROM fixed_finish_date)=" + note.day + " ";
if (hasPrev)
{
getQuery += " AND ";
getQuery += extractDay;
}
else
{
getQuery += "WHERE " + extractDay;
}
hasPrev = true;
}
#endregion
#region Report info
if (!string.IsNullOrWhiteSpace(note.description))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += "fixed_report SIMILAR TO '%" + note.description + "%' ";
}
else
{
getQuery += "WHERE fixed_report SIMILAR TO '%" + note.description + "%' ";
}
hasPrev = true;
}
#endregion
#region Serial
if (!string.IsNullOrWhiteSpace(note.userTicketSerial))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_serial='" + note.userTicketSerial.ToString() + "' ";
}
else
{
getQuery += "WHERE u_serial='" + note.userTicketSerial.ToString() + "' ";
}
hasPrev = true;
}
#endregion
#region Number
if (!string.IsNullOrWhiteSpace(note.userTicketNumber))
{
if (hasPrev)
{
getQuery += " AND ";
getQuery += " u_number SIMILAR TO '%" + note.userTicketNumber + "%' ";
}
else
{
getQuery += "WHERE u_number SIMILAR TO'%" + note.userTicketNumber + "%' ";
}
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(fromReaderToFixJournal(reader));
}
}
}
return(list.AsEnumerable());
}
