public List<BookRegisterDTO> GetAll()
{
List<BookRegisterDTO> listRegister = new List<BookRegisterDTO>();
try
{
SqlDataReader reader = ConnectionManager.GetCommand("SP1001All", new Dictionary<string, SqlDbType>(), new List<object>()).ExecuteReader();
BookRegisterDTO register;
while (reader.Read())
{
register = new BookRegisterDTO();
register.UserId = reader["UserID"].ToString();
register.ISBN = reader["ISBN"].ToString();
register.RegisterDate = (DateTime)reader["RegisterDate"];
register.Note = reader["Note"].ToString();
int status;
int.TryParse(reader["Status"].ToString(), out status);
register.Status = (BookRegisterStatus)Enum.Parse(typeof(BookRegisterStatus), status.ToString());
register.CreatedDate = (DateTime)reader["CreatedDate"];
register.UpdatedDate = (DateTime)reader["UpdatedDate"];
listRegister.Add(register);
}
}
catch (Exception e)
{
Log.Error("Error at BookRegisterDAO - GetAll", e);
return null;
}
return listRegister;
}
public List<BookRegisterDTO> SearchRequestAllStt(SearchRequestDTO dto)
{
BookRegisterDTO bookReg;
List<BookRegisterDTO> list = new List<BookRegisterDTO>();
try
{
SqlDataReader reader = ConnectionManager.GetCommand("sp0003AllStt",
new Dictionary<string, SqlDbType>()
{
{"@userName", SqlDbType.NVarChar},
{"@title", SqlDbType.NVarChar},
{"@fromDate", SqlDbType.DateTime},
{"@toDate", SqlDbType.DateTime}
},
new List<object>()
{
dto.UserName,
dto.Title,
dto.FromDate,
dto.ToDate
}).ExecuteReader();
while (reader.Read())
{
bookReg = new BookRegisterDTO();
bookReg.Username = reader["Username"].ToString();
bookReg.BookTitle = reader["Title"].ToString();
bookReg.RegisterDate = DateTime.Parse(reader["RegisterDate"].ToString());
bookReg.Note = reader["Note"].ToString();
int status;
int.TryParse(reader["Status"].ToString(), out status);
bookReg.Status = (BookRegisterStatus)Enum.Parse(typeof(BookRegisterStatus), status.ToString());
list.Add(bookReg);
}
reader.Close();
}
catch (Exception e)
{
Log.Error("Error at AuthorDAO - GetAuthorByID", e);
return null;
}
return list;
}
public bool RejectRequest(BookRegisterDTO registerDto)
{
try
{
registerDto.Status = BookRegisterStatus.REJECTED;
return registerBus.UpdateBookRegister(registerDto);
}
catch (Exception e)
{
Log.Error("Error at Feature - RejectRequest", e);
}
return false;
}
public string ImportRegister(FileStream fileStream)
{
var msg = new StringBuilder();
// read data from excel file
List<List<object>> data = null;
try
{
data = ParseExcelUtil.ReadExcelFile(fileStream);
}
catch (Exception e)
{
Log.Error("Error at Feature - ImportRegister - Parse excel", e);
msg.AppendLine("Có lỗi xảy ra khi đọc dữ liệu!");
return msg.ToString();
}
// Process data
// set i = 1 to omit the header row
for (int i = 1; i < data.Count; i++)
{
var registerDto = new BookRegisterDTO();
registerDto.UserId = data[i][0].ToString();
registerDto.ISBN = data[i][1].ToString();
try
{
registerDto.RegisterDate = DateTime.Parse(data[i][2].ToString());
}
catch (Exception)
{
msg.AppendLine("Có lỗi xảy ra [Dòng thứ " + i + "][RegisterDate không hợp lệ]");
continue;
}
registerDto.Note = data[i][3].ToString();
registerDto.Status = BookRegisterStatus.NEW;
// Validate data
if (registerBus.GetBookRegisterByPrimaryKey(registerDto.UserId, registerDto.ISBN, registerDto.RegisterDate) != null)
{
msg.AppendLine("Có lỗi xảy ra [Dòng thứ " + i + "] [Dữ liệu đã tồn tại]");
continue;
}
if (userBus.GetUserById(registerDto.UserId) == null)
{
msg.AppendLine("Có lỗi xảy ra [Dòng thứ " + i + "] [UserID không tồn tại]");
continue;
}
if (catalogeBus.GetCatalogueById(registerDto.ISBN) == null)
{
msg.AppendLine("Có lỗi xảy ra [Dòng thứ " + i + "] [ISBN không tồn tại]");
continue;
}
// Insert data to database
try
{
registerBus.InsertBookRegister(registerDto);
}
catch (Exception e)
{
msg.AppendLine("Có lỗi xảy ra [Dòng thứ " + i + "] [Lưu dữ liệu thất bại]");
Log.Error("Error at Feature - ImportRegister", e);
continue;
}
}
return msg.ToString();
}
public string ApproveRequest(BookRegisterDTO registerDto)
{
try
{
// Update BookCopy Status
var listAvailableCopy = GetAvailableCopy(registerDto.ISBN);
if (listAvailableCopy.Count <= 0)
{
return "Không còn sách khả dụng!";
}
// Update Register status
registerDto.Status = BookRegisterStatus.APPROVED;
if (!registerBus.UpdateBookRegister(registerDto))
{
return Constants.SYSTEM_ERROR;
}
// Add Rental
var rentalDto = new RentalDTO();
rentalDto.UserId = registerDto.UserId;
rentalDto.Barcode = listAvailableCopy[0].Barcode;
rentalDto.Status = RentalStatus.NEW;
rentalDto.IssueDate = DateTime.Now;
rentalDto.DueDate = DateTime.Now.AddDays(7);
AddRental(rentalDto);
// Send mail to Reader
var userDto = userBus.GetUserById(registerDto.UserId);
if (userDto != null)
{
var writer = new StringWriter();
var html = new HtmlTextWriter(writer);
html.WriteEncodedText(String.Format("Thân gửi {0}.", userDto.Username));
html.WriteEncodedText(" Yều cầu của bạn đã được chấp nhận.");
html.WriteEncodedText(String.Format(" Tên sách: {0}", registerDto.BookTitle));
html.WriteEncodedText(String.Format(" Ngày đăng ký: {0}", registerDto.RegisterDate.ToShortDateString()));
html.WriteEncodedText(String.Format(" Ngày yêu cầu được chấp nhận: {0}", DateTime.Now.ToShortDateString()));
html.WriteEncodedText(" Mời bạn đến nhận sách.");
SendSampleMail(userDto.Email, Options.MailSubject, writer.ToString());
}
}
catch (Exception e)
{
Log.Error("Error at Feature - ApproveRequest", e);
}
return null;
}
public bool AddRegister(BookRegisterDTO registerDto)
{
Log.Info("Add new Register");
try
{
registerDto.Status = BookRegisterStatus.NEW;
return registerBus.InsertBookRegister(registerDto);
}
catch (Exception e)
{
Log.Error("Error at Feature - AddRegister", e);
}
return false;
}
public bool UpdateBookRegister(BookRegisterDTO bookRegisterDto)
{
return _bookRegisterDao.Update(bookRegisterDto);
}
public bool InsertBookRegister(BookRegisterDTO bookRegister)
{
return _bookRegisterDao.Insert(bookRegister);
}
public bool Update(BookRegisterDTO bookRegister)
{
try
{
bookRegister.UpdatedDate = DateTime.Now;
ConnectionManager.GetCommand("SP1003",
new Dictionary<string, SqlDbType>()
{
{"@UserID", SqlDbType.NVarChar},
{"@ISBN", SqlDbType.NVarChar},
{"@RegisterDate", SqlDbType.DateTime},
{"@Note", SqlDbType.NText},
{"@Status", SqlDbType.Int},
{"@CreatedDate", SqlDbType.DateTime},
{"@UpdatedDate", SqlDbType.DateTime}
},
new List<object>()
{
bookRegister.UserId,
bookRegister.ISBN,
bookRegister.RegisterDate,
bookRegister.Note,
bookRegister.Status,
bookRegister.CreatedDate,
bookRegister.UpdatedDate
}).ExecuteNonQuery();
return true;
}
catch (Exception e)
{
Log.Error("Error at BookRegisterDAO - Update", e);
return false;
}
}
public List<BookRegisterDTO> GetByUserIdPaging(string userId, int start, int end, out int noR)
{
List<BookRegisterDTO> listRegister = new List<BookRegisterDTO>();
try
{
noR = (int)ConnectionManager.GetCommand("SP1001ByUserIDPagingC",
new Dictionary<string, SqlDbType>()
{
{"@UserID", SqlDbType.NVarChar},
{"@StartRow", SqlDbType.NVarChar},
{"@EndRow", SqlDbType.NVarChar}
},
new List<object>()
{
userId,
start,
end
}).ExecuteScalar();
if (noR != 0)
{
SqlDataReader reader = ConnectionManager.GetCommand("SP1001ByUserIDPaging",
new Dictionary<string, SqlDbType>()
{
{"@UserID", SqlDbType.NVarChar},
{"@StartRow", SqlDbType.NVarChar},
{"@EndRow", SqlDbType.NVarChar}
},
new List<object>()
{
userId,
start,
end
}).ExecuteReader();
BookRegisterDTO register;
while (reader.Read())
{
register = new BookRegisterDTO();
register.UserId = reader["UserID"].ToString();
register.ISBN = reader["ISBN"].ToString();
register.RegisterDate = (DateTime) reader["RegisterDate"];
register.Note = reader["Note"].ToString();
int status;
int.TryParse(reader["Status"].ToString(), out status);
register.Status =
(BookRegisterStatus) Enum.Parse(typeof (BookRegisterStatus), status.ToString());
register.CreatedDate = (DateTime) reader["CreatedDate"];
register.UpdatedDate = (DateTime) reader["UpdatedDate"];
listRegister.Add(register);
}
}
}
catch (Exception e)
{
Log.Error("Error at BookRegisterDAO - GetByUserID", e);
noR = 0;
return null;
}
return listRegister;
}
public List<BookRegisterDTO> GetByStatusAndCreatedDate(BookRegisterStatus status, DateTime createdDate)
{
var listRegister = new List<BookRegisterDTO>();
try
{
SqlDataReader reader = ConnectionManager.GetCommand("SP1001ByStatusAndCreatedDate",
new Dictionary<string, SqlDbType>()
{
{"@Status", SqlDbType.Int},
{"@CreatedDate", SqlDbType.DateTime}
},
new List<object>()
{
(int) status,
createdDate
}).ExecuteReader();
BookRegisterDTO register;
while (reader.Read())
{
register = new BookRegisterDTO();
register.UserId = reader["UserID"].ToString();
register.ISBN = reader["ISBN"].ToString();
register.RegisterDate = (DateTime)reader["RegisterDate"];
register.Note = reader["Note"].ToString();
int statusTmp;
int.TryParse(reader["Status"].ToString(), out statusTmp);
register.Status = (BookRegisterStatus)Enum.Parse(typeof(BookRegisterStatus), status.ToString());
register.CreatedDate = (DateTime)reader["CreatedDate"];
register.UpdatedDate = (DateTime)reader["UpdatedDate"];
listRegister.Add(register);
}
}
catch (Exception e)
{
Log.Logger.Error("Error at BookRegisterDAO - GetByStatusAndCreatedDate", e);
return null;
}
return listRegister;
}
public BookRegisterDTO GetByPrimaryKey(string userId, string isbn, DateTime registerDate)
{
BookRegisterDTO registerDto = null;
try
{
SqlDataReader reader = ConnectionManager.GetCommand("SP1001ByPrimaryKey",
new Dictionary<string, SqlDbType>()
{
{"@UserID", SqlDbType.NVarChar},
{"@ISBN", SqlDbType.NVarChar},
{"@RegisterDate", SqlDbType.DateTime}
},
new List<object>()
{
userId,
isbn,
registerDate
}).ExecuteReader();
if (reader.Read())
{
registerDto = new BookRegisterDTO();
registerDto.UserId = reader["UserID"].ToString();
registerDto.ISBN = reader["ISBN"].ToString();
registerDto.RegisterDate = (DateTime)reader["RegisterDate"];
registerDto.Note = reader["Note"].ToString();
int status;
int.TryParse(reader["Status"].ToString(), out status);
registerDto.Status = (BookRegisterStatus)Enum.Parse(typeof(BookRegisterStatus), status.ToString());
registerDto.CreatedDate = (DateTime)reader["CreatedDate"];
registerDto.UpdatedDate = (DateTime)reader["UpdatedDate"];
}
}
catch (Exception e)
{
Log.Error("Error at BookRegisterDAO - GetByRegisterDate", e);
return null;
}
return registerDto;
}
