C# 综合示例 库存管理系统11 入库管理（FormStorageIn）

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的

图99A-26入库管理窗口设计

由于存在一个入库单对应多个入库物的情况，因此提供了两种显示方式。“入库单”按钮按下显示的是入库单信息，“入库货物”按钮按下显示的是入库货物信息。datagridview在显示入库单时，双击弹出入库单ID对应的入库单操作窗口；datagridview在显示入库货物信息时，双击转到显示该入库货物对应的入库单。

全部代码如下：

        OleDbConnection connection;

        DataSet ds;

        OleDbDataAdapter adapter;

        //每页显示记录数

        const int pagesize = 50;

        //总的页数

        int maxPage;

        //当前页数

        int currentPage;

        FormMain F_Main;

        //入库单查询相关sql语句

        string sqlOrderSelect;

        string sqlOrderFrom;

        string sqlOrderOrder;

        string sqlOrderWhere;

        //入库货物查询相关sql语句

        string sqlFoodsSelect;

        string sqlFoodsFrom;   

        string sqlFoodsOrder;

        string sqlFoodsWhere;

        //以入库物自定义1查询相关语句

        string sqlQuerySelect;

        string sqlQueryFrom;

        string sqlQueryOrder;

        string sqlQueryWhere;

        //以入库单自定义2查询相关语句

        string sqlQueryOrderSelect;

        string sqlQueryOrderFrom;

        string sqlQueryOrderOrder;

        string sqlQueryOrderWhere;

        string sqlFlag;

        private void FormStorageIn_Load(object sender, EventArgs e)

        {

            WindowState = FormWindowState.Maximized;

            connection = new OleDbConnection(classMod.databaseConnString);

            connection.Open();

            F_Main = (FormMain)MdiParent;

            sqlOrderSelect = "SELECT 入库单.入库单ID, 操作员.姓名 as 操作员, 入库单.订购日期, 入库单.入库日期 ";

            sqlOrderFrom = "FROM 操作员 INNER JOIN 入库单 ON 操作员.ID = 入库单.操作员ID ";

            sqlOrderWhere = "where 入库单.是否删除='否'";

            sqlOrderOrder = "order by 入库单.入库单ID";

            sqlFoodsSelect = "Select  入库单.入库单ID, 操作员.姓名 as 操作员, 货物信息.产品名称 as 货物, 入库单明细.单价, 入库单明细.数量, 货物信息.单位数量, 供应商.公司名称 as 供应商 ";

            sqlFoodsFrom = "From 操作员 INNER Join (((货物信息 INNER Join 供应商 On 货物信息.供应商ID = 供应商.供应商ID) INNER Join 入库单明细 On 货物信息.产品ID = 入库单明细.产品ID) INNER Join 入库单 On 入库单明细.入库单ID = 入库单.入库单ID) ON 操作员.ID = 入库单.操作员ID ";

            sqlFoodsWhere = "where 入库单.是否删除='否'";

            sqlFoodsOrder = "order by 入库单明细.入库单ID";

            sqlQuerySelect = "Select  入库单.入库单ID, 操作员.姓名 as 操作员, 货物信息.产品名称 as 货物, 入库单明细.单价, 入库单明细.数量, 货物信息.单位数量, 供应商.公司名称 as 供应商 ";

            sqlQueryFrom = "From 操作员 INNER Join (((货物信息 INNER Join 供应商 On 货物信息.供应商ID = 供应商.供应商ID) INNER Join 入库单明细 On 货物信息.产品ID = 入库单明细.产品ID) INNER Join 入库单 On 入库单明细.入库单ID = 入库单.入库单ID) ON 操作员.ID = 入库单.操作员ID ";

            sqlQueryWhere = "";

            sqlQueryOrder = "order by 入库单明细.入库单ID";

            sqlQueryOrderSelect = "SELECT 入库单.入库单ID, 操作员.姓名 as 操作员, 入库单.订购日期, 入库单.入库日期 ";

            sqlQueryOrderFrom = "FROM 操作员 INNER JOIN 入库单 ON 操作员.ID = 入库单.操作员ID ";

            sqlQueryOrderOrder = "";

            sqlQueryOrderWhere = "order by 入库单.入库单ID";

            sqlFlag = "入库单";

            showGrid(sqlFlag);

        }

        private void tsbStockInOrder_Click(object sender, EventArgs e)

        {

            sqlFlag = "入库单";

            showGrid(sqlFlag);

            //在查询入库物品单时，才允许增改删

            tsbAdd.Enabled = true;

            tsbEdit.Enabled = true;

            tsbDelete.Enabled = true;

        }

        //显示查询的数据信息

        private void showGrid(string tableFlag)

        {

            ds = new DataSet(tableFlag);

            //建立OleDbCommand，传入Sql语句求要查询记录的总数

            OleDbCommand command = new OleDbCommand();

            string sqlSelect = "";

            string sqlFrom = "";

            string sqlWhere = "";

            string sqlOrder = "";

            switch (tableFlag)

            {

                case "入库单":

                    sqlSelect = sqlOrderSelect;

                    sqlFrom = sqlOrderFrom;

                    sqlWhere = sqlOrderWhere;

                    sqlOrder = sqlOrderOrder;

                    break;

                case "入库物":

                    sqlSelect = sqlFoodsSelect;

                    sqlFrom = sqlFoodsFrom;

                    sqlWhere = sqlFoodsWhere;

                    sqlOrder = sqlFoodsOrder;

                    break;

                case "自定义1":

                    sqlSelect = sqlQuerySelect;

                    sqlFrom = sqlQueryFrom;

                    sqlWhere = sqlQueryWhere;

                    sqlOrder = sqlQueryOrder;

                    break;

                case "自定义2":

                    sqlSelect = sqlQueryOrderSelect;

                    sqlFrom = sqlQueryOrderFrom;

                    sqlWhere = sqlQueryOrderWhere;

                    sqlOrder = sqlQueryOrderOrder;

                    break;

            }

            command.CommandText = "select count(*) " + sqlFrom + sqlWhere;

            command.Connection = connection;

            int count = (int)command.ExecuteScalar();

            F_Main.tsslInfo.Text = "数据总数：" + count + " 条";

            if (count < pagesize)

            {

                tsbFirstPage.Enabled = false;

                tsbPrevPage.Enabled = false;

                tsbNextPage.Enabled = false;

                tsbLastPage.Enabled = false;

            }

            else

            {

                tsbFirstPage.Enabled = true;

                tsbPrevPage.Enabled = true;

                tsbNextPage.Enabled = true;

                tsbLastPage.Enabled = true;

            }

            command.CommandText = sqlSelect +

                            sqlFrom +

                            sqlWhere +

                            sqlOrder;

            adapter = new OleDbDataAdapter(command);

            Console.WriteLine(command.CommandText);

            //根据记录总数得到总的页数，其中Math.Ceiling返回大于或等于指定的数的最小整数值

            maxPage = (int)Math.Ceiling((double)count / pagesize);

            currentPage = 0;

            adapter.Fill(ds, currentPage * pagesize, pagesize, tableFlag);

            //防止列顺序与查询顺序不一致，需要加入以下一行代码

            dgv.DataSource = null;

            dgv.DataSource = ds.Tables[tableFlag];

            tslRecord.Text = (currentPage + 1).ToString() + "/" + maxPage.ToString();

        }

        private void tsbStockInFoods_Click(object sender, EventArgs e)

        {

            sqlFlag = "入库物";

            showGrid(sqlFlag);

            //在查询入库物品时，禁止增改删

            tsbAdd.Enabled = false;

            tsbEdit.Enabled = false;

            tsbDelete.Enabled = false;

        }

        private void tsbQuery_Click(object sender, EventArgs e)

        {

            FormStorageInQuery F_StorageInQuery = new FormStorageInQuery();

            F_StorageInQuery.Owner = this;

            F_StorageInQuery.ShowDialog();

            if (customWhere == "")

                return;

            sqlQueryWhere = "where " + customWhere + " and (入库单.是否删除='否')";

            sqlFlag = "自定义1";

            showGrid(sqlFlag);

        }

       //第一页数据

        private void tsbFirstPage_Click(object sender, EventArgs e)

        {

            currentPage = 0;

            //先清除已经填入的表，否则会将数据追加到已有表

            ds.Tables[sqlFlag].Clear();

            adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag);

            dgv.DataSource = ds.Tables[sqlFlag];

            tslRecord.Text = "1/" + maxPage.ToString();

        }

        //上一页数据

        private void tsbPrevPage_Click(object sender, EventArgs e)

        {

            if (currentPage == 0)

            {

                MessageBox.Show("已经是第一页了");

                return;

            }

            currentPage -= 1;

            ds.Tables[sqlFlag].Clear();

            adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag);

            dgv.DataSource = ds.Tables[sqlFlag];

            tslRecord.Text = (currentPage + 1).ToString() + "//" + maxPage.ToString();

        }

        //下一页数据

        private void tsbNextPage_Click(object sender, EventArgs e)

        {

            if (currentPage == maxPage - 1)

            {

                MessageBox.Show("已经是最后一页了");

                return;

            }

            currentPage += 1;

            ds.Tables[sqlFlag].Clear();

            int recordcount = adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag);

            dgv.DataSource = ds.Tables[sqlFlag];

            tslRecord.Text = (currentPage + 1).ToString() + "//" + maxPage.ToString();

        }

        //最后一页数据

        private void tsbLastPage_Click(object sender, EventArgs e)

        {

            currentPage = maxPage - 1;

            ds.Tables[sqlFlag].Clear();

            adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag);

            dgv.DataSource = ds.Tables[sqlFlag];

            tslRecord.Text = (currentPage + 1).ToString() + "//" + maxPage.ToString();

        }

        //增加入库单

        private void tsbAdd_Click(object sender, EventArgs e)

        {

            FormStorageInOrder F_StorageInOrder = new FormStorageInOrder(0);

            F_StorageInOrder.ShowDialog();

        }

        //查看或修改入库单

        private void tsbEdit_Click(object sender, EventArgs e)

        {

            if (dgv.SelectedRows.Count == 0)

            {

                MessageBox.Show("请先选择一条入库单");

                return;

            }

            int orderid;

            orderid = (int)dgv.SelectedRows[0].Cells[0].Value;

            FormStorageInOrder F_StorageInOrder = new FormStorageInOrder(orderid);

            F_StorageInOrder.ShowDialog();

        }

        //删除入库单。。只能删除登录操作员本人的

        private void tsbDelete_Click(object sender, EventArgs e)

        {

            if (dgv.SelectedRows.Count == 0)

            {

                MessageBox.Show("请先选择一条入库单");

                return;

            }

            int orderid;

            orderid = (int)dgv.SelectedRows[0].Cells[0].Value;

            OleDbCommand command = new OleDbCommand();

            command.Connection = connection;

            //先查询该入库单记录是否当前操作员保存

            command.CommandText = "select 操作员ID from 入库单 where 入库单ID=" + orderid;

            OleDbDataReader odReader = command.ExecuteReader(CommandBehavior.SingleResult);

            odReader.Read();

            int recordUserID = odReader.GetInt32(0);

            if (recordUserID != classMod.loginId)

            {

                MessageBox.Show("不允许删除其它操作员的入库单");

                return;

            }

            odReader.Close();

            if (MessageBox.Show("确定要删除此条记录？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.Cancel)

                return;

            //将该入库单标记为删除（实际未删除）

            command.CommandText = "update 入库单 set 是否删除=//是// where 入库单ID=" + orderid;

            //删除不用返回值，直接使用ExecuteNonQuery。

            command.ExecuteNonQuery();

            //从dgv中删除该行

            dgv.Rows.Remove(dgv.SelectedRows[0]);

        }

        private void tsbHome_Click(object sender, EventArgs e)

        {

            Close();

        }

        private void FormStorageIn_FormClosing(object sender, FormClosingEventArgs e)

        {

            connection.Close();

        }

        //=====================================

        //在dgv中双击某行

        private void dgv_MouseDoubleClick(object sender, MouseEventArgs e)

        {

            DataGridView.HitTestInfo dgvhti = dgv.HitTest(e.X, e.Y);

            int selectedIndex;

            if (dgvhti.Type == DataGridViewHitTestType.Cell || dgvhti.Type == DataGridViewHitTestType.RowHeader)

            {

                selectedIndex = dgvhti.RowIndex;

                int orderid = (int)dgv.Rows[selectedIndex].Cells[0].Value;

                if (sqlFlag == "入库物" || sqlFlag == "自定义1")

                {

                    //当在入库物 表格时，双击转到查询对应的订单

                    sqlQueryOrderWhere = "where (入库单.入库单ID=" + orderid + ") and (入库单.是否删除='否')";

                    sqlFlag = "自定义2";

                    showGrid(sqlFlag);

                    //在查询入库单时，才允许增改删

                    tsbAdd.Enabled = true;

                    tsbEdit.Enabled = true;

                    tsbDelete.Enabled = true;

                }

                else

                {

                    //当在 入库单、自定义2 时，双击同选中某行后按下“修改”按钮的效果

                    FormStorageInOrder F_StorageInOrder = new FormStorageInOrder(orderid);

                    F_StorageInOrder.ShowDialog();

                }

            }

            else

            {

                return;

            }

        }

        //自定义查询，需要从FormStockOutQuery返回查询条件

        public string customWhere;

        //自定义查询

        private void tsbQuery_Click_1(object sender, EventArgs e)

        {

            FormStorageInQuery F_StorageInQuery = new FormStorageInQuery();

            F_StorageInQuery.Owner = this;

            F_StorageInQuery.ShowDialog();

            if (customWhere == "")

                return;

            sqlQueryWhere = "where " + customWhere + " and (入库单.是否删除='否')";

            sqlFlag = "自定义1";

            showGrid(sqlFlag);

        }

学习更多vb.net知识，请参看vb.net 教程 目录

学习更多C#知识，请参看C#教程 目录