// 续借
public int RenewBook(BorrowReturn b)
{
string sql = @"update BorrowReturn set ReturnTime=dateadd(month,3,ReturnTime),RenewCount=RenewCount+1 where BorrowId=@BorrowId";
SqlParameter[] sp =
{
new SqlParameter("@BorrowId", b.BorrowId)
};
return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
}
//借书
private void dgvBookInfo_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string BookId = "";
try
{
//选中行的图书编号
BookId = dgvBookInfo.Rows[e.RowIndex].Cells[0].Value.ToString();
}
catch (Exception) { }
//DataGridView的总列数
int rows = dgvBookInfo.Columns.Count;
if (e.ColumnIndex == rows - 1)//借书
{
if (dgvReaderInfo.Rows.Count < 1)
{
MessageBox.Show("没有读者信息！");
return;
}
BorrowReturn b = new BorrowReturn();
b.BookId = BookId;
b.ReaderId = dgvReaderInfo.Rows[0].Cells[0].Value.ToString();
b.BorrowTime = DateTime.Now;
b.ReturnTime = DateTime.Now;//数据库中存储过程根据借书时间自动计算应还书日期
b.Fine = 0;
b.RenewCount = 0;
b.BorrowRemark = "";
DialogResult result = MessageBox.Show("确定借书吗？", "提醒", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
if (borrowReturn_bll.BorrowBook(b) == 0)
{
MessageBox.Show("借书成功！");
//刷新图书借还表
dgvReaderInfo_DataSourceChanged(null, null);
//选中添加成功的新行
autocoumns.AutoFindRow(b.BorrowId, dgvBorrowed);
//刷新 //读者信息与读者借还表的联动
txtBookId_TextChanged(null, null);
}
else
{
MessageBox.Show("借书失败！");
}
}
}
}
private void dgvBorrowed_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int BorrowReturnId = -1;
try
{
//选中行的借阅编号
BorrowReturnId = (int)dgvBorrowed.Rows[e.RowIndex].Cells[0].Value;
}
catch (Exception) { }
//DataGridView的总列数
int rows = dgvBorrowed.Columns.Count;
if (e.ColumnIndex == rows - 2)//修改
{
DialogResult result = MessageBox.Show("确定续借吗？", "提醒", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
BorrowReturn b = new BorrowReturn();
b.BorrowId = BorrowReturnId;
if (borrowReturn_bll.RenewBook(b) > 0)
{
MessageBox.Show("续借成功！");
dgvReaderInfo_DataSourceChanged(null, null);
autocoumns.AutoFindRow(b.BorrowId, dgvBorrowed);
}
else
{
MessageBox.Show("续借失败！");
}
}
}
else if (e.ColumnIndex == rows - 1)//还书
{
DialogResult result = MessageBox.Show("确定还书吗？", "提醒", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
if (borrowReturn_bll.ReturnBook(BorrowReturnId) > 0)
{
dgvReaderInfo_DataSourceChanged(null, null);
MessageBox.Show("还书成功！");
txtBookId_TextChanged(null, null);
}
else
{
MessageBox.Show("还书失败！");
}
}
}
}
//查询借还表信息
public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime)
{
string sql = string.Format(@"select BookInfo.BookId as 'BookId',Reader.ReaderId as 'ReaderId',BookName,ReaderName,
BookTypeName,ReaderTypeName,Gender,IdentityCard,
BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount
from BorrowReturn
inner join BookInfo on BookInfo.BookId=BorrowReturn.BookId
inner join Reader on Reader.ReaderId=BorrowReturn.ReaderId
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
inner join ReaderType on ReaderType.ReaderTypeId=Reader.ReaderTypeId
where BookInfo.BookId like '%{0}%' and
Reader.ReaderId like '%{1}%' ", b.BookId, b.ReaderId);
if (radioName == "全部")
{
}
else if (radioName == "已借")
{
sql += " and FactReturnTime is null ";
}
else if (radioName == "已还")
{
sql += " and FactReturnTime is not null ";
}
if (checkTime)
{
if (cboBorrowTimeType == "日期")
{
sql += string.Format(@" and BorrowId in(
select BorrowId from BorrowReturn where BorrowTime between '{0}' and '{1}' union
select BorrowId from BorrowReturn where ReturnTime between '{0}' and '{1}' union
select BorrowId from BorrowReturn where FactReturnTime between '{0}' and '{1}'
) ", b.TimeIn, b.TimeOut);
}
else if (cboBorrowTimeType == "借书日期")
{
sql += @" and BorrowTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
else if (cboBorrowTimeType == "应还书日期")
{
sql += @" and ReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
else if (cboBorrowTimeType == "实际还书日期")
{
sql += @" and FactReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' ";
}
}
return(DBhelp.Create().ExecuteAdater(sql));
}
private void BtnRegisterReturn_Click(object sender, EventArgs e)
{
try
{
if (borrBO != null)
{
brBO = new BorrowReturn();
brBLL = new BorrowReturnBLL();
bill = new Bill();
billBLL = new BillBLL();
brBO.BorrowId = borrBO.BorrowId;
brBO.ReturnDate = DateTime.Now;
brBO.Comment = "ska tash";
brBO.InsBy = FormLoggedUser.Id;
if (PayBill)
{
bill.BillingDate = DateTime.Now;
bill.BillTypeId = 2;
bill.MaterialId = borrBO.materialId;
bill.SubscriberId = borrBO.SubscriberId;
bill.Price = delayfee;
bill.InsBy = FormLoggedUser.Id;
brBO.BillId = billBLL.Add(bill);
if (brBO.BillId < 1)
{
throw new Exception();
}
}
brBLL.Add(brBO);
}
else
{
MessageBox.Show("Material wasn't provided");
}
}
catch (Exception ex)
{
MessageBox.Show("Material is not returned, contact your administrator");
}
}
// 借书
public int BorrowBook(BorrowReturn b)
{
string sql = "proc_BorrowBook";
SqlParameter[] sp =
{
new SqlParameter("@BorrowId", DbType.Int32),
new SqlParameter("@BookId", b.BookId),
new SqlParameter("@ReaderId", b.ReaderId),
new SqlParameter("@BorrowTime", b.BorrowTime),
new SqlParameter("@ReturnTime", b.ReturnTime),
new SqlParameter("@Fine", b.Fine),
new SqlParameter("@RenewCount", b.RenewCount),
new SqlParameter("@BorrowRemark", b.BorrowRemark),
new SqlParameter("@ReturnValue", DbType.Int32)
};
sp[0].Direction = ParameterDirection.Output;
sp[sp.Length - 1].Direction = ParameterDirection.ReturnValue;
DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
b.BorrowId = (int)sp[0].Value;
return((int)sp[sp.Length - 1].Value);
}
// 单击查询
private void btnSelect_Click(object sender, EventArgs e)
{
BorrowReturn b = new BorrowReturn();
b.BookId = txtBookId.Text.Trim();
b.ReaderId = txtReaderId.Text.Trim();
b.TimeIn = dtIn.Value;
b.TimeOut = dtOut.Value;
String rdoName = rdoAll.Text;
if (rdoBorrow.Checked)
{
rdoName = rdoBorrow.Text;
}
else if (rdoReturn.Checked)
{
rdoName = rdoReturn.Text;
}
String cboBorrowTimeType = this.cboBorrowTimeType.SelectedItem.ToString();
bool checkTime = this.checkTime.Checked;
dgvHostory.DataSource = borrowReturn_bll.selectHostory(b, rdoName, cboBorrowTimeType, checkTime).Tables[0];
}
//查询BorrowReturn表信息
public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime)
{
string sql = string.Format(@"select BookInfo.BookId as 'BookId',Reader.UserId as 'UserId',BookName,UserName,BookTypeName,UserTypeName,Gender,IdentityCard,BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount from BorrowReturn
inner join BookInfo on BookInfo.BookId=BorrowReturn.BookId
inner join Reader on Reader.UserId=BorrowReturn.UserId
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
inner join ReaderType on ReaderType.UserTypeId=Reader.UserTypeId
where BookInfo.BookId like '%{0}%' and
Reader.UserId like '%{1}%' ", b.BookId, b.UserId);
if (radioName == "全部")
{
}
else if (radioName == "已借")
{
sql += " and FactReturnTime is null ";
}
else if (radioName == "已还")
{
sql += " and FactReturnTime is not null ";
}
return(DBhelp.Create().ExecuteAdater(sql));
}
//续借
public int RenewBook(BorrowReturn b)
{
return(borrowReturn_dal.RenewBook(b));
}
//借书
public int BorrowBook(BorrowReturn b)
{
return(borrowReturn_dal.BorrowBook(b));
}
//查询借还表信息
public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime)
{
return(borrowReturn_dal.selectHostory(b, radioName, cboBorrowTimeType, checkTime));
}
static void Main(string[] args)
{
//SubscriberBLL sb = new SubscriberBLL();
// sb.GetExpiredSubscribersEmail();
//EmailService em = new EmailService();
//em.SendMails("*****@*****.**", "send from c# Endrit Tmava", "Funksionoi dergimi i email nga Library Management");
//em.SendMails("*****@*****.**", "send from c# Endrit Tmava", "Funksionoi dergimi i email nga Library Management");
// RoleBLL rolebll;
// List<Role> role;
// rolebll = new RoleBLL();
// role = rolebll.GetAllRoles();
// //comboRoleCreate.DataSource = role;
// string errors = "";
// User usr = new User();
// usr.Name = "a";
// usr.LastName = "a";
// usr.Email = "a";
// usr.Username = "******";
// usr.InsBy = FormLoggedUser.Id;
// usr.Password = "******";
// //usr._role = getRole();
// //usr.RoleID = usr._role.UserRoleId;
// UsersValidation usrval = new UsersValidation();
// usrval.validateCreateUser();
// ValidationResult vres = usrval.Validate(usr);
// if (vres.IsValid == false)
// {
// foreach (ValidationFailure item in vres.Errors)
// {
// errors += $" {item.ErrorMessage} \n \n";
// }
// MessageBox.Show(errors,"ERROR WARNING",MessageBoxButtons.OK,MessageBoxIcon.Warning);
// }
//}
//public Role getRole()
//{
// return comboRoleCreate.SelectedItem as Role;
//}\
//Material mat = new Material();
//MaterialBLL mbll = new MaterialBLL();
//mat.MaterialId = 1020;
//mat.Title = "tryagain";
//mat._Genre.GenreId = 1;
//mat._PublishHouse._PublishHouse = "Shpijajem";
//mat.PublishPlace = "FFK";
//mat._MaterialType.MaterialTypeId = 1;
//mat.AvailableCoppies = 4;
//mat.Quantity = 4;
//mat._Language.LanguageId = 1;
//mat.LanguageId = 1;
//mat.UpdBy = 80;
//mat._Author.AuthorName = "Endrittmavahe";
//Console.WriteLine(mbll.Update(mat).ToString());
BorrowBLL borrow = new BorrowBLL();
BorrowReturn br = new BorrowReturn();
br.BorrowReturnId = 3;
br.BorrowId = 2;
br.ReturnDate = DateTime.Now;
br.Comment = "asdenaaa";
br.UpdBy = 80;
BorrowReturnBLL brb = new BorrowReturnBLL();
brb.Update(br);
//foreach (var item in borrow.GetAll())
//{
// Console.WriteLine($"{item.SubscriberId}, {item.materialId}, {item.DeadLine}");
//}
}