/// <summary>
/// 绑定书架
/// </summary>
/// <param name="Flag">为1表示绑定querybookcase,为2表示绑定bookcase,为3表示绑定cdcase</param>
private void BindBookcase(int Flag)
{
SqlConnection connect = InitConnect.GetConnection();
SqlDataAdapter bookcase = new SqlDataAdapter("select * from bookcase", connect);
DataSet data = new DataSet();
bookcase.Fill(data, "bookcase");
if (Flag == 1)
{
this.querybookcase.DataSource = data.Tables["bookcase"];
this.querybookcase.DisplayMember = "bookcase_place";
this.querybookcase.ValueMember = "bookcase_place";
}
else if (Flag == 2)
{
this.bookcase.DataSource = data.Tables["bookcase"];
this.bookcase.DisplayMember = "bookcase_place";
this.bookcase.ValueMember = "bookcase_place";
}
else
{
this.cdcase.DataSource = data.Tables["bookcase"];
this.cdcase.DisplayMember = "bookcase_place";
this.cdcase.ValueMember = "bookcase_place";
}
}
/// <summary>
/// 导入 供应商信息
/// </summary>
private void ProviderDataIn()
{
SqlConnection connect = InitConnect.GetConnection();
try
{
connect.Open();
SqlCommand cmd = new SqlCommand();
if (this.small.Checked)
{
cmd = new SqlCommand("insert into provider(provider_id,provider_name,provider_address,provider_post,provider_tel,provider_fax) select provider_id,provider_name,provider_address,provider_post,provider_tel,provider_fax from opendatasource('Microsoft.Jet.Oledb.4.0','Extended Properties=Excel 5.0;Data Source=" + this.openFileDialog1.FileName + "')...[excel$]", connect);
}
else
{
cmd = new SqlCommand("insert into provider(provider_id,provider_name,provider_address,provider_post,provider_tel,provider_fax) select provider_id,provider_name,provider_address,provider_post,provider_tel,provider_fax from opendatasource('Microsoft.ACE.Oledb.12.0','Extended Properties=Excel 12.0;Data Source=" + this.openFileDialog1.FileName + "')...[excel$]", connect);
}
cmd.ExecuteNonQuery();
MessageBox.Show("成功导入了供应商信息！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("导入供应商信息失败！\n错误信息：" + ee.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
finally
{
connect.Close();
}
}
private void ShowPower()//列出用户的权限
{
for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
{
this.checkedListBox1.SetItemCheckState(i, CheckState.Unchecked);
}
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("select * from power where power_style=@style", connect);
cmd.Parameters.AddWithValue("@style", this.userstyle.Text.Trim());
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
for (int i = 0; i < this.checkedListBox1.Items.Count; i++)
{
if (this.checkedListBox1.Items[i].ToString().Trim() == read["power_name"].ToString().Trim())
{
this.checkedListBox1.SetItemCheckState(i, CheckState.Checked);
break;
}
}
}
read.Close();
connect.Close();
}
/// <summary>
/// 显示光盘的详细信息
/// </summary>
private void DisplayCd()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("select * from cd_info where cd_id=@id", connect);
cmd.Parameters.AddWithValue("@id", this.cdid.Text.Trim());
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
this.cdcode.Text = read["cd_code"].ToString().Trim();
this.cdname.Text = read["cd_name"].ToString().Trim();
this.cdstyle.Text = read["cd_style"].ToString().Trim();
this.cdauthor.Text = read["cd_author"].ToString().Trim();
this.cdpublish.Text = read["cd_publish"].ToString().Trim();
this.cdisbn.Text = read["cd_isbn"].ToString().Trim();
this.cddate.Value = DateTime.Parse(read["cd_date"].ToString().Trim());
this.cdinprice.Text = read["cd_inprice"].ToString().Trim();
this.cdoutprice.Text = read["cd_outprice"].ToString().Trim();
this.cdcase.Text = read["cd_bookcase"].ToString().Trim();
this.cddatein.Value = DateTime.Parse(read["cd_in"].ToString().Trim());
this.cdmemo.Text = read["cd_memo"].ToString().Trim();
}
else
{
this.CdClear(false);
}
read.Close();
connect.Close();
}
/// <summary>
/// 确定交易
/// </summary>
private void SaleOk()
{
if (this.listView1.Items.Count != 0)
{
for (int i = 0; i < this.listView1.Items.Count; i++)
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("SaleWare", connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@flag", this.book.Checked?this.book.Text.Trim():this.cd.Text.Trim());
cmd.Parameters.AddWithValue("@id", this.listView1.Items[i].SubItems[1].Text.Trim());
cmd.Parameters.AddWithValue("@count", int.Parse(this.listView1.Items[i].SubItems[4].Text.Trim()));
cmd.Parameters.AddWithValue("@date", DateTime.Parse(DateTime.Now.ToShortDateString()));
cmd.Parameters.AddWithValue("@who", UserInfo.UserID);
cmd.Parameters.AddWithValue("@vip", this.yes.Checked ? this.vip.Text.Trim() : "");
cmd.ExecuteNonQuery();
connect.Close();
}
MessageBox.Show("恭喜您，交易成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
this.listView1.Items.Clear();
this.money.Text = "0.0";
}
else
{
MessageBox.Show("没有购买任何商品无法交易！！！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
}
/// <summary>
/// 添加或修改时自动设置会员可用的优惠政策
/// </summary>
private void SetRebate()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("SetVipRebate", connect);
cmd.CommandType = CommandType.StoredProcedure;
if (this.style.Enabled == false)
{
cmd.Parameters.AddWithValue("@rebate", decimal.Parse(((DataRowView)source.Current).Row.ItemArray[1].ToString().Trim()));
}
else
{
cmd.Parameters.AddWithValue("@rebate", 1);
}
SqlDataReader read = cmd.ExecuteReader();
this.rebate.Items.Clear();
if (this.style.Enabled == false)
{
this.rebate.Items.Add(((DataRowView)source.Current).Row.ItemArray[1].ToString().Trim());
}
while (read.Read())
{
this.rebate.Items.Add(read["rebate"].ToString().Trim());
}
read.Close();
connect.Close();
}
/// <summary>
/// 初始化数据
/// </summary>
private void InitData()
{
SqlConnection connect = InitConnect.GetConnection();
SqlCommand select = new SqlCommand();
SqlCommand insert = new SqlCommand();
SqlCommand delete = new SqlCommand();
bookcase.SelectCommand = select;
bookcase.SelectCommand.Connection = connect;
bookcase.SelectCommand.CommandText = "select bookcase_place as 书架位置 from bookcase where 1=0";
//////////////////////////////////////////////////////////
bookcase.InsertCommand = insert;
bookcase.InsertCommand.Connection = connect;
bookcase.InsertCommand.CommandText = "insert into bookcase(bookcase_place) values(@place)";
bookcase.InsertCommand.Parameters.Add("@place", SqlDbType.VarChar, 0, "书架位置");
//////////////////////////////////////////////////////////
bookcase.DeleteCommand = delete;
bookcase.DeleteCommand.Connection = connect;
bookcase.DeleteCommand.CommandText = "delete bookcase where bookcase_place=@place";
bookcase.DeleteCommand.Parameters.Add("@place", SqlDbType.VarChar, 0, "书架位置");
//////////////////////////////////////////////////////////
bookcase.Fill(data, "bookcase");
source.DataSource = data;
source.DataMember = "bookcase";
this.dataGridView1.DataSource = source;
///////////////////////////////////////////////////////////
this.place.DataBindings.Add("Text", source, "书架位置", true);
}
/// <summary>
/// 初始化数据
/// </summary>
private void InitData()
{
SqlConnection connect = InitConnect.GetConnection();
SqlCommand select = new SqlCommand();
SqlCommand insert = new SqlCommand();
SqlCommand delete = new SqlCommand();
user_style.SelectCommand = select;
user_style.SelectCommand.Connection = connect;
user_style.SelectCommand.CommandText = "select userstyle_name as 用户类型 from user_style where 1=0";
///////////////////////////////////////////////////////////////
user_style.InsertCommand = insert;
user_style.InsertCommand.Connection = connect;
user_style.InsertCommand.CommandText = "insert into user_style(userstyle_name) values(@name)";
user_style.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar, 0, "用户类型");
/////////////////////////////////////////////////////////////
user_style.DeleteCommand = delete;
user_style.DeleteCommand.Connection = connect;
user_style.DeleteCommand.CommandText = "delete user_style where userstyle_name=@name";
user_style.DeleteCommand.Parameters.Add("@name", SqlDbType.VarChar, 0, "用户类型");
/////////////////////////////////////////////////////////
user_style.Fill(data, "user_style");
source.DataSource = data;
source.DataMember = "user_style";
this.dataGridView1.DataSource = source;
////////////////////////////////////////////////////////////
this.style.DataBindings.Add("Text", source, "用户类型", true);
}
private void ok_Click(object sender, EventArgs e)//备份
{
if (this.filename.Text.Trim() != "")
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("backup database " + InitConnect.GetDatabaseName() + " to disk='" + this.filename.Text.Trim() + "' with init", connect);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("您成功备份了数据库！", "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
this.Close();
}
catch (Exception ee)
{
MessageBox.Show("备份失败！\n错误信息：" + ee.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
finally
{
connect.Close();
}
}
else
{
MessageBox.Show("请输入正确的路径及备份文件名称！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
}
private void warereturn_Click(object sender, EventArgs e)//顾客退货
{
if (this.view.Items.Count != 0)
{
for (int i = 0; i < this.view.Items.Count; i++)
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("WareReturn", connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@style", this.book.Checked?this.book.Text.Trim():this.cd.Text.Trim());
cmd.Parameters.AddWithValue("@id", this.view.Items[i].SubItems[1].Text.Trim());
cmd.Parameters.AddWithValue("@sale", DateTime.Parse(this.saledate.Value.ToShortDateString()));
cmd.Parameters.AddWithValue("@date", DateTime.Parse(DateTime.Now.ToShortDateString()));
cmd.Parameters.AddWithValue("@count", int.Parse(this.count.Text.Trim()));
cmd.Parameters.AddWithValue("@who", UserInfo.UserID);
cmd.Parameters.AddWithValue("@memo", this.reason.Text.Trim());
cmd.ExecuteNonQuery();
connect.Close();
}
this.view.Items.Clear();
this.ReturnCount();
this.ReturnMoney();
MessageBox.Show("顾客退货成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
else
{
MessageBox.Show("没有顾客来退货！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
}
}
private void Ok_Click(object sender, EventArgs e)//登录系统
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("select count(*) from book_user where user_id=@user and user_pwd=@pwd and user_style=@style", connect);
cmd.Parameters.AddWithValue("@user", this.textBox1.Text.Trim());
cmd.Parameters.AddWithValue("@pwd", MD5.MD5String(this.textBox2.Text.Trim()));
cmd.Parameters.AddWithValue("@style", this.comboBox1.Text.Trim());
int count = int.Parse(cmd.ExecuteScalar().ToString());
if (count != 0)
{
UserInfo.UserID = this.textBox1.Text.Trim();
UserInfo.UserPower = this.comboBox1.Text.Trim();
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("用户名或密码错误！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
this.comboBox1.Focus();
this.textBox1.SelectAll();
this.textBox2.Text = "";
}
connect.Close();
}
/// <summary>
/// 显示排行
/// </summary>
private void Display()
{
this.index.Items.Clear();
//////////////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("SaleIndex", connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ware", this.book.Checked?this.book.Text.Trim():this.cd.Text.Trim());
cmd.Parameters.AddWithValue("@flag", this.style.SelectedIndex);
SqlDataReader read = cmd.ExecuteReader();
int count = 1;
while (read.Read())
{
ListViewItem item = this.index.Items.Add(read[0].ToString().Trim());
for (int i = 1; i < read.FieldCount; i++)
{
item.SubItems.Add(read[i].ToString().Trim());
}
item.SubItems.Add(count.ToString().Trim());
count++;
}
read.Close();
connect.Close();
}
/// <summary>
/// 导入图书信息
/// </summary>
private void Book_InfoDataIn()
{
SqlConnection connect = InitConnect.GetConnection();
try
{
connect.Open();
SqlCommand cmd = new SqlCommand();
if (this.small.Checked)
{
cmd = new SqlCommand("insert into book_info(book_id,book_code,book_name,book_style,book_author,book_publish,book_isbn,book_inprice,book_outprice,book_page,book_bookcase,book_count,book_date,book_in,book_memo,book_cd_case) select book_id,book_code,book_name,book_style,book_author,book_publish,book_isbn,book_inprice,book_outprice,book_page,book_bookcase,book_count,book_date,book_in,book_memo,book_cd_case from opendatasource('Microsoft.Jet.Oledb.4.0','Extended Properties=Excel 5.0;Data Source=" + this.openFileDialog1.FileName + "')...[excel$]", connect);
}
else
{
cmd = new SqlCommand("insert into book_info(book_id,book_code,book_name,book_style,book_author,book_publish,book_isbn,book_inprice,book_outprice,book_page,book_bookcase,book_count,book_date,book_in,book_memo,book_cd_case) select book_id,book_code,book_name,book_style,book_author,book_publish,book_isbn,book_inprice,book_outprice,book_page,book_bookcase,book_count,book_date,book_in,book_memo,book_cd_case from opendatasource('Microsoft.ACE.Oledb.12.0','Extended Properties=Excel 12.0;Data Source=" + this.openFileDialog1.FileName + "')...[excel$]", connect);
}
cmd.ExecuteNonQuery();
MessageBox.Show("成功导入了图书信息！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
catch (Exception ee)
{
MessageBox.Show("导入图书信息失败！\n错误信息：" + ee.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
finally
{
connect.Close();
}
}
/// <summary>
/// 设置用户的权限
/// </summary>
private void SetUserPower()
{
this.button1.Enabled = false;
this.menublock1.Enabled = false;
this.button2.Enabled = false;
this.menublock2.Enabled = false;
this.button3.Enabled = false;
this.menublock3.Enabled = false;
this.button4.Enabled = false;
this.menublock4.Enabled = false;
this.button5.Enabled = false;
this.menublock5.Enabled = false;
this.button6.Enabled = false;
this.menublock6.Enabled = false;
/////////////////////////////////////////////////////////////////////////////////////////
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("select * from power where power_style=@style", connect);
cmd.Parameters.AddWithValue("@style", UserInfo.UserPower);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
if (read["power_name"].ToString().Trim() == "系统设置")
{
this.button1.Enabled = true;
this.menublock1.Enabled = true;
}
else if (read["power_name"].ToString().Trim() == "基本资料维护")
{
this.button2.Enabled = true;
this.menublock2.Enabled = true;
}
else if (read["power_name"].ToString().Trim() == "采购管理")
{
this.button3.Enabled = true;
this.menublock3.Enabled = true;
}
else if (read["power_name"].ToString().Trim() == "销售管理")
{
this.button4.Enabled = true;
this.menublock4.Enabled = true;
}
else if (read["power_name"].ToString().Trim() == "库存管理")
{
this.button5.Enabled = true;
this.menublock5.Enabled = true;
}
else if (read["power_name"].ToString().Trim() == "系统数据管理")
{
this.button6.Enabled = true;
this.menublock6.Enabled = true;
}
}
read.Close();
connect.Close();
}
/// <summary>
/// 检查是否有越界商品,如果有弹出警告窗口
/// </summary>
private void CheckWareUpDown()
{
if (InitConnect.GetWareDown(true) != "0" || InitConnect.GetWareDown(false) != "0" || InitConnect.GetWareUp(true) != "0" || InitConnect.GetWareUp(false) != "0")
{
FrmWarning warning = new FrmWarning();
warning.Show();
}
}
/// <summary>
/// 还原数据库前use其它数据库
/// </summary>
private void UseOtherDatabase()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("use master", connect);
cmd.ExecuteNonQuery();
connect.Close();
}
private void dataout_Click(object sender, EventArgs e)//导出
{
if (this.bookinfo.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select book_id as 图书编号,book_code as 条形码,book_name as 图书名称,book_style as 图书类型,book_author as 作者,book_publish as 出版社,book_isbn as ISBN,book_inprice as 进价,book_outprice as 售价,book_page as 页码,book_bookcase as 书架,book_count as 库存量,convert(varchar(10),book_date,120) as 出版时间,convert(varchar(10),book_in,120) as 入库时间,book_memo as 图书简介,book_cd_case as 图书附加光盘所在书架 from book_info");
}
else if (this.cdinfo.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select cd_id as 光盘编号,cd_code as 条形码,cd_name as 光盘名称,cd_style as 光盘类型,cd_author as 作者,cd_publish as 出版社,cd_isbn as ISBN,cd_inprice as 进价,cd_outprice as 售价,cd_bookcase as 书架,cd_count as 库存量,convert(varchar(10),cd_date,120) as 出版时间,convert(varchar(10),cd_in,120) as 入库时间,cd_memo as 光盘简介 from cd_info");
}
else if (this.vip.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select vip_id as 会员编号,vip_name as 姓名,vip_sex as 性别,vip_tel as 电话,vip_address as 地址,vip_identity as 身份证号,vip_style as 会员类型,convert(varchar(10),vip_date,120) as 会员办理时间 from vip");
}
else if (this.provider.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select provider_id as 供应商编号,provider_name as 姓名,provider_address as 地址,provider_post as 邮政编码,provider_tel as 联系电话,provider_fax as 传真 from provider");
}
else if (this.booksale.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select book_sale.book_sale_id as 图书编号,book_sale.book_sale_code as 条形码,book_info.book_name as 图书名称,book_info.book_publish as 出版社,book_info.book_isbn as ISBN,convert(varchar(10),book_sale.book_sale_date,120) as 销售日期,book_sale.book_sale_count as 销售数量,book_user.user_id as 操作员号,book_user.user_name as 操作员姓名,book_sale.book_sale_vip as 会员号 from book_info inner join book_sale on book_info.book_id=book_sale.book_sale_id inner join book_user on book_sale.book_sale_who=book_user.user_id");
}
else if (this.cdsale.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select cd_sale.cd_sale_id as 光盘编号,cd_sale.cd_sale_code as 条形码,cd_info.cd_name as 光盘名称,cd_info.cd_publish as 出版社,cd_info.cd_isbn as ISBN,convert(varchar(10),cd_sale.cd_sale_date,120) as 销售日期,cd_sale.cd_sale_count as 销售数量,book_user.user_id as 操作员号,book_user.user_name as 操作员姓名,cd_sale.cd_sale_vip as 会员号 from cd_info inner join cd_sale on cd_info.cd_id=cd_sale.cd_sale_id inner join book_user on cd_sale.cd_sale_who=book_user.user_id");
}
else if (this.bookback.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select book_back.book_back_id as 图书编号,book_back.book_back_code as 条形码,book_info.book_name as 图书名称,book_info.book_publish as 出版社,book_info.book_isbn as ISBN,book_back.book_back_count as 退还数量,convert(varchar(10),book_back.book_back_date,120) as 退还日期 from book_back inner join book_info on book_info.book_id=book_back.book_back_id");
}
else if (this.cdback.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select cd_back.cd_back_id as 光盘编号,cd_back.cd_back_code as 条形码,cd_info.cd_name as 光盘名称,cd_info.cd_publish as 出版社,cd_info.cd_isbn as ISBN,cd_back.cd_back_count as 退还数量,convert(varchar(10),cd_back.cd_back_date,120) as 退还日期 from cd_back inner join cd_info on cd_back.cd_back_id=cd_info.cd_id");
}
else if (this.bookbad.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select book_bad.book_bad_id as 图书编号,book_bad.book_bad_code as 条形码,book_info.book_name as 图书名称,book_info.book_publish as 出版社,book_info.book_isbn as ISBN,book_bad.book_bad_count as 损坏数量,book_bad.book_bad_memo as 备注 from book_bad inner join book_info on book_bad.book_bad_id=book_info.book_id");
}
else if (this.cdbad.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select cd_bad.cd_bad_id as 光盘编号,cd_bad.cd_bad_code as 条形码,cd_info.cd_name as 光盘名称,cd_info.cd_publish as 出版社,cd_info.cd_isbn as ISBN,cd_bad.cd_bad_count as 损坏数量,cd_bad.cd_bad_memo as 备注 from cd_bad inner join cd_info on cd_bad.cd_bad_id=cd_info.cd_id");
}
else if (this.bookreturn.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select book_return.book_return_id as 图书编号,book_return.book_return_code as 条形码,book_info.book_name as 光盘名称,book_info.book_publish as 出版社,book_info.book_isbn as ISBN,convert(varchar(10),book_return.book_return_sale,120) as 购买时间,convert(varchar(10),book_return.book_return_date,120) as 退货时间,book_return.book_return_count as 退货数量,book_user.user_id as 操作员号,book_user.user_name as 操作员姓名,book_return.book_return_memo as 备注 from book_info inner join book_return on book_info.book_id=book_return_id inner join book_user on book_user.user_id=book_return.book_return_who");
}
else if (this.cdreturn.Checked)
{
InitConnect.DataOut(this.saveFileDialog1, "select cd_return.cd_return_id as 光盘编号,cd_return.cd_return_code as 条形码,cd_info.cd_name as 光盘名称,cd_info.cd_publish as 出版社,cd_info.cd_isbn as ISBN,convert(varchar(10),cd_return.cd_return_sale,120) as 购买时间,convert(varchar(10),cd_return.cd_return_date,120) as 退货时间,cd_return.cd_return_count as 退货数量,book_user.user_id as 操作员号,book_user.user_name as 操作员姓名,cd_return.cd_return_memo as 备注 from cd_info inner join cd_return on cd_info.cd_id=cd_return.cd_return_id inner join book_user on cd_return.cd_return_who=book_user.user_id");
}
else
{
MessageBox.Show("请选择你要导出的数据项！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
}
private void InitData()
{
SqlConnection connect = InitConnect.GetConnection();
SqlCommand select = new SqlCommand();
SqlCommand insert = new SqlCommand();
SqlCommand update = new SqlCommand();
SqlCommand delete = new SqlCommand();
vip.SelectCommand = select;
vip.SelectCommand.Connection = connect;
vip.SelectCommand.CommandText = "select vip_id as 会员号,vip_name as 会员姓名,vip_sex as 性别,vip_tel as 电话,vip_address as 地址,vip_identity as 身份证号,vip_style as 会员类型,vip_date as 办理时间 from vip where 1=0";
/////////////////////////////////////////////////////////
vip.InsertCommand = insert;
vip.InsertCommand.Connection = connect;
vip.InsertCommand.CommandText = "insert into vip(vip_id,vip_name,vip_sex,vip_tel,vip_address,vip_identity,vip_style,vip_date) values(@id,@name,@sex,@tel,@address,@identity,@style,@date)";
vip.InsertCommand.Parameters.Add("@id", SqlDbType.Char, 0, "会员号");
vip.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar, 0, "会员姓名");
vip.InsertCommand.Parameters.Add("@sex", SqlDbType.Char, 0, "性别");
vip.InsertCommand.Parameters.Add("@tel", SqlDbType.VarChar, 0, "电话");
vip.InsertCommand.Parameters.Add("@address", SqlDbType.VarChar, 0, "地址");
vip.InsertCommand.Parameters.Add("@identity", SqlDbType.Char, 0, "身份证号");
vip.InsertCommand.Parameters.Add("@style", SqlDbType.VarChar, 0, "会员类型");
vip.InsertCommand.Parameters.Add("@date", SqlDbType.DateTime, 0, "办理时间");
//////////////////////////////////////////////////////////
vip.UpdateCommand = update;
vip.UpdateCommand.Connection = connect;
vip.UpdateCommand.CommandText = "update vip set vip_name=@name,vip_sex=@sex,vip_tel=@tel,vip_address=@address,vip_identity=@identity,vip_style=@style,vip_date=@date where vip_id=@id";
vip.UpdateCommand.Parameters.Add("@id", SqlDbType.Char, 0, "会员号");
vip.UpdateCommand.Parameters.Add("@name", SqlDbType.VarChar, 0, "会员姓名");
vip.UpdateCommand.Parameters.Add("@sex", SqlDbType.Char, 0, "性别");
vip.UpdateCommand.Parameters.Add("@tel", SqlDbType.VarChar, 0, "电话");
vip.UpdateCommand.Parameters.Add("@address", SqlDbType.VarChar, 0, "地址");
vip.UpdateCommand.Parameters.Add("@identity", SqlDbType.Char, 0, "身份证号");
vip.UpdateCommand.Parameters.Add("@style", SqlDbType.VarChar, 0, "会员类型");
vip.UpdateCommand.Parameters.Add("@date", SqlDbType.DateTime, 0, "办理时间");
///////////////////////////////////////////////////
vip.DeleteCommand = delete;
vip.DeleteCommand.Connection = connect;
vip.DeleteCommand.CommandText = "delete vip where vip_id=@id";
vip.DeleteCommand.Parameters.Add("@id", SqlDbType.Char, 0, "会员号");
//////////////////////////////////////////////////////////////
vip.Fill(data, "vip");
source.DataSource = data;
source.DataMember = "vip";
this.dataGridView1.DataSource = source;
/////////////////////////////////////////////
this.id.DataBindings.Add("Text", source, "会员号", true);
this.name.DataBindings.Add("Text", source, "会员姓名", true);
this.sex.DataBindings.Add("Text", source, "性别", true);
this.tel.DataBindings.Add("Text", source, "电话", true);
this.address.DataBindings.Add("Text", source, "地址", true);
this.identity.DataBindings.Add("Text", source, "身份证号", true);
this.style.DataBindings.Add("SelectedValue", source, "会员类型", true);
this.date.DataBindings.Add("Value", source, "办理时间", true);
}
/// <summary>
/// 是否设置了系统的上下限
/// </summary>
/// <returns>返回true表示设置了，否则表示没有设置</returns>
private bool IsConfig()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("select count(*) from book_updown", connect);
string result = cmd.ExecuteScalar().ToString().Trim();
connect.Close();
return(result != "0");
}
private void DisplayStyle()//列出所有用户的类型
{
SqlConnection connect = InitConnect.GetConnection();
DataSet data = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select * from user_style", connect);
adapter.Fill(data, "user_style");
this.comboBox1.DataSource = data.Tables["user_style"];
this.comboBox1.DisplayMember = "userstyle_name";
this.comboBox1.ValueMember = "userstyle_name";
}
/// <summary>
/// 绑定用户类型
/// </summary>
private void BindUserStyle()
{
SqlConnection connect = InitConnect.GetConnection();
SqlDataAdapter adapter = new SqlDataAdapter("select * from user_style", connect);
DataSet dataset = new DataSet();
adapter.Fill(dataset, "user_style");
this.style.DataSource = dataset.Tables["user_style"];
this.style.DisplayMember = "userstyle_name";
this.style.ValueMember = "userstyle_name";
}
///<summary>
///绑定书架
///</summary>
private void BindBookcase()
{
SqlConnection connect = InitConnect.GetConnection();
SqlDataAdapter bookcase = new SqlDataAdapter("select * from bookcase", connect);
DataSet data = new DataSet();
bookcase.Fill(data, "bookcase");
this.bookcase.DataSource = data.Tables["bookcase"];
this.bookcase.DisplayMember = "bookcase_place";
this.bookcase.ValueMember = "bookcase_place";
}
/// <summary>
/// 默认设置
/// </summary>
private void DefaultConfig()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("insert into back_return(back_return_name,back_return_count) values('光盘',0)", connect);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("insert into back_return(back_return_name,back_return_count) values('图书',0)", connect);
cmd.ExecuteNonQuery();
connect.Close();
}
private void cddown_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)//光盘低于下限
{
if (InitConnect.GetWareDown(false).Trim() == "0")
{
MessageBox.Show("没有光盘低于系统下限！", "显示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
else
{
PrintUpDown cddown = new PrintUpDown(4, "光盘");
cddown.Show();
}
}
private void FrmWarning_Load(object sender, EventArgs e)//初始化
{
int StartX = Screen.PrimaryScreen.WorkingArea.Width - this.Width;
int StartY = Screen.PrimaryScreen.Bounds.Height;
this.Location = new Point(StartX, StartY);
//////////////////////////////////////////////////////////////////////////
this.bookupcount.Text = InitConnect.GetWareUp(true) + " 本";
this.cdupcount.Text = InitConnect.GetWareUp(false) + " 张";
this.bookdowncount.Text = InitConnect.GetWareDown(true) + " 本";
this.cddowncount.Text = InitConnect.GetWareDown(false) + " 张";
}
/// <summary>
/// 默认设置
/// </summary>
private void DefaultConfig()
{
SqlConnection connect = InitConnect.GetConnection();
connect.Open();
SqlCommand cmd = new SqlCommand("insert into book_updown(updown_style,updown_count) values('上限',0)", connect);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("insert into book_updown(updown_style,updown_count) values('下限',0)", connect);
cmd.ExecuteNonQuery();
connect.Close();
}
/// <summary>
/// 显示图书的详细信息
/// </summary>
private void BindBook()
{
SqlConnection connect = InitConnect.GetConnection();
DataSet data = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select book_id as 图书编号,book_code as 条形码,book_name as 图书名称,book_style as 图书类型,book_publish as 出版社,book_count as 库存量 from book_info", connect);
data.Clear();
adapter.Fill(data, "book_info");
booksource = new BindingSource(data, "book_info");
booksource.Filter = "图书编号 like '" + this.bookid.Text.Trim() + "%'";
this.bookshowinfo.DataSource = booksource;
}
/// <summary>
/// 显示光盘的详细信息
/// </summary>
private void BindCd()
{
SqlConnection connect = InitConnect.GetConnection();
DataSet data = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select cd_id as 光盘编号,cd_code as 条形码,cd_name as 光盘名称,cd_style as 光盘类型,cd_publish as 出版社,cd_count as 库存量 from cd_info", connect);
data.Clear();
adapter.Fill(data, "cd_info");
cdsource = new BindingSource(data, "cd_info");
cdsource.Filter = "光盘编号 like '" + this.cdid.Text.Trim() + "%'";
this.cdshowinfo.DataSource = cdsource;
}
private void bookdown_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)//图书低于下限
{
if (InitConnect.GetWareDown(true).Trim() == "0")
{
MessageBox.Show("没有图书低于系统下限！", "显示", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
}
else
{
PrintUpDown bookdown = new PrintUpDown(3, "图书");
bookdown.Show();
}
}
private void BindUserStyle()//绑定用户类型
{
SqlConnection connect = InitConnect.GetConnection();
SqlDataAdapter book_user = new SqlDataAdapter("select * from user_style", connect);
DataSet data = new DataSet();
data.Clear();
book_user.Fill(data, "user_style");
this.userstyle.DataSource = data.Tables["user_style"];
this.userstyle.DisplayMember = "userstyle_name";
this.userstyle.ValueMember = "userstyle_name";
}