AnsoNekoの博客

前言

• 因为工作需要，公司需要进行盘点固定资产，于是开发此程序
• 由于比较懒所以不做登录模块，实现主体功能即可

开发环境

1. Windows 10
2. IDE：visual studio 2019
3. 语言：C#【这不是废话吗，标题都说了】

设计思路

需求分析

• 程序可以根据唯一条码查询固定资产
• 程序可以对固定资产进行盘点，并设定盘点条件
• 程序可以查询历史盘点记录，处理上次未盘点数据
• 程序可以导入、导出、修改资产数据

界面设计

• 查询界面

• 盘点界面

• 盘点历史

• 新增资产

代码实现

查询模块

• 考虑到操作方便以及对扫码枪的支持，所以在设计的时候没有设计查询按钮，而是通过检测回车事件进行查询，用户按下回车键，程序即根据条码框数据进行查询。

        private void codeBox_KeyUp(object sender, KeyEventArgs e)
        {
            string query;
            if (e.KeyCode == Keys.Control || e.KeyCode == Keys.Enter)
            {
                //MessageBox.Show(codeBox.Text.Trim(), "回车键", MessageBoxButtons.OK,MessageBoxIcon.Information);
                query = $"CALL selectbycode(\"{codeBox.Text.Trim()}\");";
                MySqlCommand myCommand = common.ExcuteQuery(query);
                MySqlDataReader myReader = myCommand.ExecuteReader();
                if (myReader.Read())
                {
                    searchName.Text = myReader["name"].ToString();
                    searchType.Text = myReader["type"].ToString();
                    searchCode.Text = myReader["code"].ToString();
                    searchApart.Text = myReader["apartment"].ToString();
                    searchDate.Text = myReader["date"].ToString();
                }
                codeBox.Text = "";
                codeBox.Focus();
            }
        }

盘点模块

• 根据用户选择的条件开始进行数据检索并展示到listview中，若有未处理的未盘点数据，则提示用户处理

        private void StartCheck_Click(object sender, EventArgs e)
        {
            this.checkList.Items.Clear();
            string name, type, apartment, query, code, checkTime;
            int i;
            i = checkLast();
            if (i == 1)
            {
                i = 0;
                name = $"%{this.nameCombox.Text}%";
                type = $"%{this.typeCombox.Text}%";
                apartment = $"%{this.apartCombox.Text}%";
                query = $"call checkstartselect(\"{name}\",\"{type}\",\"{apartment}\");";
                MySqlCommand myCommand = common.ExcuteQuery(query);
                MySqlDataReader myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    code = myReader["code"].ToString();
                    name = myReader["name"].ToString();
                    type = myReader["type"].ToString();
                    apartment = myReader["apartment"].ToString();
                    checkTime = common.GetTime();
                    common.checkingTime = checkTime;

                    this.checkList.Items.Add(code);
                    this.checkList.Items[i].SubItems.Add(name);
                    this.checkList.Items[i].SubItems.Add(type);
                    this.checkList.Items[i].SubItems.Add(apartment);
                    i++;

                    query = $"call insertdata(\"{code}\",\"{name}\",\"{type}\",\"{apartment}\",\"{checkTime}\");";
                    common.ExcuteQuery(query);
                }
                this.checkCode.Enabled = true;
                this.checkOver.Enabled = true;
                this.startCheck.Enabled = false;
                this.checkCode.Focus();
            }
            else
            {
                MessageBox.Show("你还有上次未盘点数据需要处理，请前往历史盘点处理后再开始新的盘点！", 
                    "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }
            
        }

• 盘点扫码，将条码数据与listview中的数据进行对比，同时标记出来，标记的方式多种，我是采用填充底色+确认符号“√”

        private void checkCode_KeyUp(object sender, KeyEventArgs e)
        {
            string inCode,query;
            int i;
            if (e.KeyCode == Keys.Control || e.KeyCode == Keys.Enter)
            {
                inCode = checkCode.Text.Trim();
                if (this.checkList.Items.Count >0 && inCode != "")
                {
                    for (i = 0; i < this.checkList.Items.Count; i++)
                    {
                        if (this.checkList.Items[i].Text == inCode)
                        {
                            this.checkList.Items[i].SubItems.Add("√");
                            this.checkList.Items[i].BackColor = Color.LightSeaGreen;
                            query = $"call checking(\"{inCode}\",\"{common.checkingTime}\",\"√\")";
                            common.ExcuteQuery(query);
                        }
                    }
                    this.checkCode.Clear();
                    this.checkCode.Focus();
                }
                else
                {
                    MessageBox.Show("找不到对应的条码！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }

• 盘点确认，当用户扫码结束，需要手动结束当前盘点任务，同时统计出用户未盘点到的数据，展示出来

        private void CheckOver_Click(object sender, EventArgs e)
        {
            string name, type, apartment, query, code;
            int i = 0;
            this.checkList.Items.Clear();
            query = $"call selectuncheck(\"{common.checkingTime}\");";
            MySqlCommand myCommand = common.ExcuteQuery(query);
            MySqlDataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                code = myReader["code"].ToString();
                name = myReader["name"].ToString();
                type = myReader["type"].ToString();
                apartment = myReader["apartment"].ToString();

                this.checkList.Items.Add(code);
                this.checkList.Items[i].SubItems.Add(name);
                this.checkList.Items[i].SubItems.Add(type);
                this.checkList.Items[i].SubItems.Add(apartment);
                this.checkList.Items[i].BackColor = Color.IndianRed;
                i++;
            }
            this.checkCode.Enabled = false;
            this.checkOver.Enabled = false;
            this.startCheck.Enabled = true;
            MessageBox.Show("盘点完毕！未盘点资产已经列出，请知晓", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

• 开始盘点时使用到的检查上次未盘点数据的function

private int checkLast()
        {
            string query, lastTime;
            query = "select lastcheck from config where sort = 1;";
            MySqlCommand myCommand = common.ExcuteQuery(query);
            MySqlDataReader myReader = myCommand.ExecuteReader();
            myReader.Read();
            lastTime = myReader["lastcheck"].ToString();
            if (lastTime != "")
            {
                query = $"call selectuncheck(\"{lastTime}\");";
                myCommand = common.ExcuteQuery(query);
                myReader = myCommand.ExecuteReader();
                if (myReader.Read())
                {
                    return 0;
                }
                else
                {
                    return 1;
                }
            }
            else
            {
                return 1;
            }
        }

盘点历史模块

• 按日期查询盘点历史，设计时为了便于查询，在盘点时就默认加入了盘点时间，此时可以利用起来

        private void Button1_Click(object sender, EventArgs e)
        {
            string historyTime,query;
            int i = 0;
            historyTime = $"{this.historyTimeCombox.Text}";
            query = $"call selecthistory(\"{historyTime}\");";
            if (historyTime == "")
            {
                query = "SELECT * FROM assetscheck;";
            }
            this.historyList.Items.Clear();
            MySqlCommand myCommand = common.ExcuteQuery(query);
            MySqlDataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                this.historyList.Items.Add(myReader["code"].ToString());
                this.historyList.Items[i].SubItems.Add(myReader["name"].ToString());
                this.historyList.Items[i].SubItems.Add(myReader["type"].ToString());
                this.historyList.Items[i].SubItems.Add(myReader["apartment"].ToString());
                this.historyList.Items[i].SubItems.Add(myReader["checked"].ToString());
                this.historyList.Items[i].SubItems.Add(myReader["checkdate"].ToString());
                i++;
            }
            this.export.Enabled = true;
        }

• 导出当前记录，为了减小导出文件大小，并避免有些PC并没有安装Excel的问题，导出格式采用csv格式

        private void Export_Click(object sender, EventArgs e)
        {
            string path;
            path = SelectPath()+"export.csv";
            ListViewToCSV(this.historyList, path, false);
            MessageBox.Show($"导出完毕！\r\n{path}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        public static void ListViewToCSV(ListView listView, string filePath, bool includeHidden)
        {
            //make header string
            StringBuilder result = new StringBuilder();
            WriteCSVRow(result, listView.Columns.Count, i => includeHidden || listView.Columns[i].Width > 0, i => listView.Columns[i].Text);

            //export data rows
            foreach (ListViewItem listItem in listView.Items)
                WriteCSVRow(result, listView.Columns.Count, i => includeHidden || listView.Columns[i].Width > 0, i => listItem.SubItems[i].Text);

            File.WriteAllText(filePath, result.ToString());
        }

        private static void WriteCSVRow(StringBuilder result, int itemsCount, Func<int, bool> isColumnNeeded, Func<int, string> columnValue)
        {
            bool isFirstTime = true;
            for (int i = 0; i < itemsCount; i++)
            {
                if (!isColumnNeeded(i))
                    continue;

                if (!isFirstTime)
                    result.Append(",");
                isFirstTime = false;

                result.Append(String.Format("\"{0}\"", columnValue(i)));
            }
            result.AppendLine();
        }

        private string SelectPath()
        {
            string path = string.Empty;
            System.Windows.Forms.FolderBrowserDialog fbd = new System.Windows.Forms.FolderBrowserDialog();
            if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                path = fbd.SelectedPath;
            }
            return path;
        }

• 未盘点数据处理，原理同盘点，只是将上一次未盘点数据查询并展示，通过选择单一项进行处理，或者进行批量处理，思路同样是对于被处理项进行底色变更+处理标记，方便用户进行区分确认，此处代码与盘点部分代码大同小异，只是将事件监测变成按键处理。

导入、修改数据

• 导入和修改均通过导入csv文件的格式进行处理，方便批量操作[其实是为了偷懒不想做专门的修改界面]

        private string SelectFile()//select your file
        {
            string path = string.Empty;
            var openFileDialog = new OpenFileDialog()
            {
                Filter = "选择文件 (*.csv)|*.csv"//如果需要筛选txt文件（"Files (*.txt)|*.txt"）
            };
            var result = openFileDialog.ShowDialog();
            if (result == DialogResult.OK)
            {
                path = openFileDialog.FileName;
                return path;
            }
            else
            {
                return "0";
            }
        }

• 读取CSV文件，并将数据展示到listview中

        public void readCSV(string filePath)
        {
            try
            {
                using (var sr = new System.IO.StreamReader(filePath,Encoding.Default))
                {
                    int j = 0;
                    while (!sr.EndOfStream)
                    {
                        string line = sr.ReadLine();
                        if (j != 0)
                        {
                            string[] values = line.Split(',');
                            //MessageBox.Show(values.Length.ToString(), "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            this.newList.Items.Add(values[0]);
                            for (int i = 1; i < values.Length; i++)
                            {
                                this.newList.Items[j-1].SubItems.Add(values[i]);
                            }
                        }
                        j++;
                    }
                }
            }
            catch (System.Exception e)
            {
                MessageBox.Show(e.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void importNew(ListView listView)//将listview中的数据导入数据库
        {
            try
            {
                string code, name, type, apartment, importTime, query;
                for (int i = 0; i < listView.Items.Count; i++)
                {
                    code = listView.Items[i].Text;
                    name = listView.Items[i].SubItems[1].Text;
                    type = listView.Items[i].SubItems[2].Text;
                    apartment = listView.Items[i].SubItems[3].Text;
                    importTime = listView.Items[i].SubItems[4].Text;
                    query = $"call insertnew(\"{code}\",\"{name}\",\"{type}\",\"{apartment}\",\"{importTime}\");";
                    common.ExcuteQuery(query);
                }
            }
            catch(System.Exception e)
            {
                MessageBox.Show(e.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            
        }

        private void Inport_Click(object sender, EventArgs e)//忽略我的错误拼写...import才是对的哈，但是我这边就不改了
        {
            string path;
            this.newList.Items.Clear();
            path = SelectFile();
            if (path != "0")
            {
                MessageBox.Show(path, "info", MessageBoxButtons.OK, MessageBoxIcon.Information);
                readCSV(path);
                importNew(this.newList);
                MessageBox.Show("导入完毕！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("选择的文件路径无效！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

• 更新数据思路与导入新数据相同，只是sql语句使用update而已，此处不再贴出。

结语

• 本程序主要部分就是这些，有一些细节性的东西因为以前的文章中已经出现过，本文就不再多写
• 可增添细节：logo，任务栏小图标及菜单 and so on...

注意事项

1. 本文中为了方便展示思路，对于数据库的操作直接写在代码中，对于实际生产环境来说这是不可行的，需要客户端与服务器分开。
2. 本文使用到的部分function未进行展示，因为其已经出现在之前的文章中
3. 源码下载

• 我的文章对你有帮助吗？喜欢的话不妨打赏我一下吧！