private void button2_Click(object sender, EventArgs e)
{
hireinfo h = new hireinfo();
h.videoid = this.textBox1.Text;
h.clientid = this.textBox2.Text;
h.borrowtime = DateTime.Now;
int rentmonth = comboBox1.SelectedIndex + 1;
h.shouldtime = DateTime.Now.AddMonths(rentmonth);
//总租金必须自己计算，不能从textBox3获取
double oneprice = bh.returnPrice(this.textBox1.Text);
double totalprice = (comboBox1.SelectedIndex + 1) * oneprice;
h.rent = totalprice;
//数据验证
if (this.textBox1.Text == "")
{
MessageBox.Show("影碟编号不能为空，请输入！");
}
else
{
if (this.textBox2.Text == "")
{
MessageBox.Show("用户编号不能为空，请输入！");
}
else
{
//先尝试录入，根据返回值提示错误
//-3(影碟不存在); -2(影碟被借完); -1(用户不存在); 0(出租失败); 1(成功); -4(未归还)
int flagReturn = bh.addHireinfo(h);
if (flagReturn > 0)
{
MessageBox.Show("出租成功，祝您生活愉快！");
this.Close();
}
else if (flagReturn == 0)
{
MessageBox.Show("出租失败，请稍后重试");
}
else if (flagReturn == -1)
{
MessageBox.Show("该用户不存在！");
}
else if (flagReturn == -2)
{
MessageBox.Show("该影碟被借完，请出租别的影碟");
}
else if (flagReturn == -3)
{
MessageBox.Show("该影碟不存在，请出租别的影碟");
}
else if (flagReturn == -4)
{
MessageBox.Show("您已经借过该影碟，还没有归还，请先归还再借该影碟！");
}
}
}
}
//修改出租信息，返回影响行数
public int updateHireinfo(hireinfo h)
{
string sql = "update hireinfo set videoid=@videoid,clientid=@clientid,isreturn=@isreturn where id=@id";
SqlParameter[] parms =
{
new SqlParameter("@id", h.id),
new SqlParameter("@videoid", h.videoid),
new SqlParameter("@clientid", h.clientid),
new SqlParameter("@isreturn", h.isreturn)
};
return(SqlHelper.ExecuteNonQuery(sql, parms));
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
int clickingColumn = e.ColumnIndex;
//点击-1—7列时，不响应
if (clickingColumn >= -1 && clickingColumn <= 7)
{
}
else
{
DataGridViewColumn column = dataGridView1.Columns[e.ColumnIndex];
string content = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
if (column.HeaderText == "删除" && content == "删除")
{
if (MessageBox.Show("确定要删除该记录吗？", "删除记录", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//删除之前先查询，否则存在关系的不允许删除
hireinfo h = new hireinfo();
string id = dataGridView1.Rows[e.RowIndex].Cells[10].Value.ToString(); //id，是隐藏的
h.id = TypeConversion.stringTOint(id);
int delflag = bh.delHireinfo(h);
if (delflag > 0) //删除成功
{
MessageBox.Show("删除成功！");
dataGridView1.Rows.RemoveAt(e.RowIndex);
}
else if (delflag == 0) //删除失败
{
MessageBox.Show("删除失败，请稍后重试！");
}
else if (delflag == -1) //不让删除
{
MessageBox.Show("该记录还被引用，不能删除！");
}
}
}
else if (column.HeaderText == "编辑" && content == "编辑")
{
string tempid = dataGridView1.Rows[e.RowIndex].Cells[10].Value.ToString();
int id = TypeConversion.stringTOint(tempid);
new hireUpdate(id).Show();
}
}
}
//删除出租信息，返回影响行数。删除之前，首先查看那些关系表中是否还有该出租的相关信息。
public int deleteHireinfo(hireinfo h)
{
string videoid = (string)SqlHelper.ExecuteScalar("select videoid from hireinfo where id=@id", new SqlParameter("@id", h.id));
string clientid = (string)SqlHelper.ExecuteScalar("select clientid from hireinfo where id=@id", new SqlParameter("@id", h.id));
string adminid = Convert.ToString(SqlHelper.ExecuteScalar("select adminid from hireinfo where id=@id", new SqlParameter("@id", h.id))); //object类型转换string
int total = this.isVideoinfo(videoid) + this.isClientinfo(clientid) + this.isAdmininfo(adminid);
//若大于0，说明其他表还有引用，不能删除
if (total > 0)
{
return(-1);
}
else
{
//根据自增id删除即可
string sql = "delete from hireinfo where id=@id";
SqlParameter parm = new SqlParameter("@id", h.id);
return(SqlHelper.ExecuteNonQuery(sql, parm));
}
}
private void button1_Click(object sender, EventArgs e)
{
hireinfo h = new hireinfo();
h.id = this.id;
h.videoid = this.textBox1.Text;
h.clientid = this.textBox2.Text;
h.isreturn = this.comboBox1.SelectedIndex;
int flagReturn = bh.upHireinfo(h); //返回值大于0，成功；等于0，保存失败。 对数据库有修改的方法，必须设置中间变量。
if (flagReturn > 0)
{
MessageBox.Show("保存成功！");
this.Close();
}
else if (flagReturn == 0)
{
MessageBox.Show("保存失败，请重试");
}
}
//出租影碟，返回受影响的行数。出租之前先验证是否可以出租；可以出租，videoinfo表数量减1
//-3(影碟不存在); -2(影碟被借完); -1(用户不存在); 0(出租失败); 1(成功); -4(未归还)
public int insertHireinfo(hireinfo h)
{
int isvideo = this.isVideoinfo(h.videoid);
int videonum = this.isVideoNumber(h.videoid);
int isclient = this.isClientinfo(h.clientid);
int isreturn = this.countIsReturn(h.videoid, h.clientid);
if (isvideo <= 0)
{
return(-3);
}
if (videonum <= 0)
{
return(-2);
}
if (isclient <= 0)
{
return(-1);
}
//未归还
if (isreturn > 0)
{
return(-4);
}
//符合出租条件，可以出租，videoinfo表数量减1
string sql = "insert into hireinfo(videoid,clientid,isreturn,borrowtime,rent,shouldtime) values(@videoid,@clientid,0,@borrowtime,@rent,@shouldtime)";
SqlParameter[] parms =
{
new SqlParameter("@videoid", h.videoid),
new SqlParameter("@clientid", h.clientid),
new SqlParameter("@borrowtime", h.borrowtime),
new SqlParameter("@rent", h.rent),
new SqlParameter("@shouldtime", h.shouldtime)
};
//videoinfo表中的数量先减1
SqlHelper.ExecuteNonQuery("update videoinfo set number = (number-1) where videoid=@videoid", new SqlParameter("@videoid", h.videoid));
return(SqlHelper.ExecuteNonQuery(sql, parms));
}
//根据影碟编号和读者编号，归还影碟，返回受影响的行数。
//若存在(isreturn=0)，hireinfo中的returntime和isreturn，videoinfo中的数量number（有多个结果时，按id排序，修改最早借的）
//若不存在，提示错误，返回-1
public int returnVideo(hireinfo h)
{
int hirenum = (int)SqlHelper.ExecuteScalar("select count(*) from hireinfo where videoid=@videoid and clientid=@clientid and isreturn=0",
new SqlParameter("@videoid", h.videoid), new SqlParameter("@clientid", h.clientid));
if (hirenum <= 0)
{
return(-1);
}
else if (hirenum == 1) //只有一个结果符合
{
string sql = "update hireinfo set returntime=@returntime,isreturn=1 where videoid=@videoid and clientid=@clientid and isreturn=0";
SqlParameter[] parms =
{
new SqlParameter("@videoid", h.videoid),
new SqlParameter("@clientid", h.clientid),
new SqlParameter("@returntime", h.returntime)
};
//videoinfo表中的数量加1
SqlHelper.ExecuteNonQuery("update videoinfo set number = (number+1) where videoid=@videoid", new SqlParameter("@videoid", h.videoid));
return(SqlHelper.ExecuteNonQuery(sql, parms));
}
else //有多个结果符合，选择最早借出的置为"归还"，即id最小的
{
string sql = "update (select *,ROW_NUMBER() over( order by id asc) as num from hireinfo where videoid=@videoid and clientid=@clientid and isreturn=0) s set returntime=@returntime,isreturn=1 where s.num=1";
SqlParameter[] parms =
{
new SqlParameter("@videoid", h.videoid),
new SqlParameter("@clientid", h.clientid),
new SqlParameter("@returntime", h.returntime)
};
//videoinfo表中的数量加1
SqlHelper.ExecuteNonQuery("update videoinfo set number = (number+1) where videoid=@videoid", new SqlParameter("@videoid", h.videoid));
return(SqlHelper.ExecuteNonQuery(sql, parms));
}
}
private void button2_Click(object sender, EventArgs e)
{
hireinfo h = new hireinfo();
h.videoid = this.textBox1.Text;
h.clientid = this.textBox2.Text;
h.returntime = DateTime.Now;
//若不存在，提示错误，返回-1
int flag = bh.returnVideo(h);
if (flag == -1)
{
MessageBox.Show("您没有租借过此影碟！");
}
else if (flag == 0)
{
MessageBox.Show("归还失败，请稍候再试！");
}
else if (flag > 0)
{
MessageBox.Show("归还成功，祝您生活愉快！");
this.Close();
}
}
//修改出租信息，是否成功
public int upHireinfo(hireinfo h)
{
return(dh.updateHireinfo(h)); //返回值大于0，成功；等于0，保存失败
}
//删除出租信息，是否成功
public int delHireinfo(hireinfo h)
{
return(dh.deleteHireinfo(h)); //返回值大于0，成功；等于0，保存失败；等于-1，不能删除
}
//返回值 -3(影碟不存在); -2(影碟被借完); -1(用户不存在); 0(出租失败); 1(成功); -4(未归还)
public int addHireinfo(hireinfo h)
{
return(dh.insertHireinfo(h));
}
//根据影碟编号和读者编号，归还影碟，返回受影响的行数。
//若存在(isreturn=0)，hireinfo中的returntime和isreturn，videoinfo中的数量number（有多个结果时，按id排序，修改最早借的）
//若不存在，提示错误，返回-1
public int returnVideo(hireinfo h)
{
return(dh.returnVideo(h));
}