11.20采购管理

using System;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Drawing;

namespace SnacksInventorySystem
{
    public partial class FormPurchaseManagement : Form
    {
        private List<Supplier> suppliers;
        private List<Goods> goodsList;

        public FormPurchaseManagement()
        {
            InitializeComponent();
            InitializeUI();
            LoadData();
        }

        private void InitializeUI()
        {
            // 设置窗口标题
            Text = "采购管理";
            // 设置窗口大小
            Width = 600;
            Height = 400;
            // 设置窗口起始位置为屏幕中央
            StartPosition = FormStartPosition.CenterScreen;

            // 创建采购日期选择器
            dateTimePickerPurchaseDate = new DateTimePicker();
            dateTimePickerPurchaseDate.Location = new Point(20, 20);
            dateTimePickerPurchaseDate.Width = 150;

            // 创建商品下拉框
            comboBoxGoods = new ComboBox();
            comboBoxGoods.Location = new Point(200, 20);
            comboBoxGoods.Width = 150;

            // 创建供应商下拉框
            comboBoxSuppliers = new ComboBox();
            comboBoxSuppliers.Location = new Point(380, 20);
            comboBoxSuppliers.Width = 150;

            // 创建采购数量输入框
            textBoxQuantity = new TextBox();
            textBoxQuantity.Location = new Point(20, 60);
            textBoxQuantity.Width = 100;

            // 创建商品单价输入框
            textBoxUnitPrice = new TextBox();
            textBoxUnitPrice.Location = new Point(200, 60);
            textBoxUnitPrice.Width = 100;

            // 创建采购总金额显示框（只读）
            textBoxTotalAmount = new TextBox();
            textBoxTotalAmount.Location = new Point(380, 60);
            textBoxTotalAmount.Width = 100;
            textBoxTotalAmount.ReadOnly = true;

            // 创建添加采购记录按钮
            Button buttonAddPurchase = new Button();
            buttonAddPurchase.Text = "添加采购记录";
            buttonAddPurchase.Location = new Point(20, 100);
            buttonAddPurchase.Click += buttonAddPurchase_Click;

            // 创建查看采购记录按钮
            Button buttonViewPurchases = new Button();
            buttonViewPurchases.Text = "查看采购记录";
            buttonViewPurchases.Location = new Point(200, 100);
            buttonViewPurchases.Click += buttonViewPurchases_Click;

            // 创建用于显示采购记录列表的DataGridView（可根据实际需求添加更多列显示详细信息）
            dataGridViewPurchases = new DataGridView();
            dataGridViewPurchases.Location = new Point(20, 140);
            dataGridViewPurchases.Width = 560;
            dataGridViewPurchases.Height = 200;
            dataGridViewPurchases.Columns.Add("PurchaseDate", "采购日期");
            dataGridViewPurchases.Columns.Add("GoodsName", "商品名称");
            dataGridViewPurchases.Columns.Add("SupplierName", "供应商名称");
            dataGridViewPurchases.Columns.Add("Quantity", "采购数量");
            dataGridViewPurchases.Columns.Add("UnitPrice", "单价");
            dataGridViewPurchases.Columns.Add("TotalAmount", "总金额");

            // 将各控件添加到采购管理窗口
            Controls.Add(dateTimePickerPurchaseDate);
            Controls.Add(comboBoxGoods);
            Controls.Add(comboBoxSuppliers);
            Controls.Add(textBoxQuantity);
            Controls.Add(textBoxUnitPrice);
            Controls.Add(textBoxTotalAmount);
            Controls.Add(buttonAddPurchase);
            Controls.Add(buttonViewPurchases);
            Controls.Add(dataGridViewPurchases);
        }

        private void LoadData()
        {
            // 加载供应商数据
            suppliers = LoadSuppliers();
            foreach (Supplier supplier in suppliers)
            {
                comboBoxSuppliers.Items.Add(supplier.Name);
            }

            // 加载商品数据
            goodsList = LoadGoods();
            foreach (Goods good in goodsList)
            {
                comboBoxGoods.Items.Add(good.Name);
            }
        }

        private List<Supplier> LoadSuppliers()
        {
            List<Supplier> result = new List<Supplier>();
            string connectionString = "Data Source=LAPTOP-ODQTAPDG\\MSSQLSERVER01;Initial Catalog=SnacksInventoryDB;User ID=root;Password=123456";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = "SELECT Supplier_id, Supplier_name, Contact_phone, Address FROM Suppliers";
                SqlCommand command = new SqlCommand(query, connection);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int id = (int)reader["Supplier_id"];
                        string name = reader["Supplier_name"].ToString();
                        string phone = reader["Contact_phone"].ToString();
                        string address = reader["Address"].ToString();
                        result.Add(new Supplier(id, name, phone, address));
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("加载供应商数据出错：" + ex.Message);
                }
            }
            return result;
        }

        private List<Goods> LoadGoods()
        {
            List<Goods> result = new List<Goods>();
            string connectionString = "Data Source=LAPTOP-ODQTAPDG\\MSSQLSERVER01;Initial Catalog=SnacksInventoryDB;User ID=root;Password=123456";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = "SELECT Goods_id, Goods_name, Unit_price, Category FROM Goods";
                SqlCommand command = new SqlCommand(query, connection);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int id = (int)reader["Goods_id"];
                        string name = reader["Goods_name"].ToString();
                        decimal price = (decimal)reader["Unit_price"];
                        string category = reader["Category"].ToString();
                        result.Add(new Goods(id, name, price, category));
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("加载商品数据出错：" + ex.Message);
                }
            }
            return result;
        }

        private void buttonAddPurchase_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                int goodsId = GetSelectedGoodsId();
                int supplierId = GetSelectedSupplierId();
                int quantity = Convert.ToInt32(textBoxQuantity.Text);
                decimal unitPrice = Convert.ToDecimal(textBoxUnitPrice.Text);
                decimal totalAmount = quantity * unitPrice;
                DateTime purchaseDate = dateTimePickerPurchaseDate.Value;

                string connectionString = "Data Source=LAPTOP-ODQTAPDG\\MSSQLSERVER01;Initial Catalog=SnacksInventoryDB;User ID=root;Password=123456";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    string query = "INSERT INTO purchase (purchase_date, goods_id, quantity, supplier_id, total_amount) VALUES (@PurchaseDate, @GoodsId, @Quantity, @SupplierId, @TotalAmount)";
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@PurchaseDate", purchaseDate);
                    command.Parameters.AddWithValue("@GoodsId", goodsId);
                    command.Parameters.AddWithValue("@Quantity", quantity);
                    command.Parameters.AddWithValue("@SupplierId", supplierId);
                    command.Parameters.AddWithValue("@TotalAmount", totalAmount);
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                        MessageBox.Show("采购记录添加成功！");
                        ClearInputFields();
                        LoadPurchaseRecords();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("添加采购记录出错：" + ex.Message);
                    }
                }
            }
        }

        private bool ValidateInput()
        {
            if (string.IsNullOrEmpty(textBoxQuantity.Text))
            {
                MessageBox.Show("请输入采购数量！");
                return false;
            }
            if (string.IsNullOrEmpty(textBoxUnitPrice.Text))
            {
                MessageBox.Show("请输入商品单价！");
                return false;
            }
            if (!int.TryParse(textBoxQuantity.Text, out _))
            {
                MessageBox.Show("采购数量必须为整数！");
                return false;
            }
            if (!decimal.TryParse(textBoxUnitPrice.Text, out _))
            {
                MessageBox.Show("商品单价格式不正确！");
                return false;
            }
            return true;
        }

    

        private int GetSelectedGoodsId()
        {
            if (comboBoxGoods.SelectedItem != null)
            {
                string selectedGoodsName = comboBoxGoods.SelectedItem.ToString();
                // 这里假设后续有根据商品名称去获取商品ID的逻辑，以下是简单示例（需根据实际数据库等操作完善）
                // 比如可以查询数据库找到对应商品名称的商品ID
                int goodsId = GetGoodsIdByName(selectedGoodsName);
                return goodsId;
            }
            else
            {
                MessageBox.Show("请先在商品下拉框中选择商品！");
                return -1; // 返回一个特殊值表示没有获取到有效的商品ID，可根据实际情况调整返回值含义
            }
        }

        private int GetGoodsIdByName(string goodsName)
        {
            // 这里是简单的模拟从数据库查询商品ID的逻辑，实际应用中需要连接数据库并执行查询语句
            // 假设存在一个Goods列表（可以是从数据库加载的，这里简化示例）
            List<Goods> goodsList = LoadGoods();
            foreach (Goods good in goodsList)
            {
                if (good.Name == goodsName)
                {
                    return good.Id;
                }
            }
            return -1; // 如果没找到对应商品名称的商品ID，返回 -1（可根据实际调整返回值含义）
        }
    
        private int GetSelectedSupplierId()
        {
            string selectedSupplierName = comboBoxSuppliers.SelectedItem.ToString();
            foreach (Supplier supplier in suppliers)
            {
                if (supplier.Name == selectedSupplierName)
                {
                    return supplier.Id;
                }
            }
            return -1;
        }

        private void ClearInputFields()
        {
            textBoxQuantity.Text = "";
            textBoxUnitPrice.Text = "";
            textBoxTotalAmount.Text = "";
            comboBoxGoods.SelectedIndex = -1;
            comboBoxSuppliers.SelectedIndex = -1;
        }

        private void buttonViewPurchases_Click(object sender, EventArgs e)
        {
            LoadPurchaseRecords();
        }

        private void LoadPurchaseRecords()
        {
            dataGridViewPurchases.Rows.Clear();
            string connectionString = "Data Source=LAPTOP-ODQTAPDG\\MSSQLSERVER01;Initial Catalog=SnacksInventoryDB;User ID=root;Password=123456";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string query = "SELECT p.purchase_date, g.goods_name, s.supplier_name, p.quantity, g.unit_price, p.total_amount " +
                               "FROM purchase p " +
                               "JOIN goods g ON p.goods_id = g.goods_id " +
                               "JOIN suppliers s ON p.supplier_id = s.supplier_id";
                SqlCommand command = new SqlCommand(query, connection);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        DateTime purchaseDate = (DateTime)reader["purchase_date"];
                        string goodsName = reader["goods_name"].ToString();
                        string supplierName = reader["supplier_name"].ToString();
                        int quantity = (int)reader["quantity"];
                        decimal unitPrice = (decimal)reader["unit_price"];
                        decimal totalAmount = (decimal)reader["total_amount"];

                        dataGridViewPurchases.Rows.Add(purchaseDate, goodsName, supplierName, quantity, unitPrice, totalAmount);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("加载采购记录出错：" + ex.Message);
                }
            }
        }

        private class Supplier
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string ContactPhone { get; set; }
            public string Address { get; set; }

            public Supplier(int id, string name, string contactPhone, string address)
            {
                Id = id;
                Name = name;
                ContactPhone = contactPhone;
                Address = address;
            }
        }

        private class Goods
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public decimal Price { get; set; }
            public string Category { get; set; }

            public Goods(int id, string name, decimal price, string category)
            {
                Id = id;
                Name = name;
                Price = price;
                Category = category;
            }
        }
        private void LoadSuppliersData()
        {
            suppliers = LoadSuppliers();
            foreach (Supplier supplier in suppliers)
            {
                comboBoxSuppliers.Items.Add(supplier.Name);
            }
        }
        private void LoadGoodsData()
        {
            goodsList = LoadGoods();
            foreach (Goods good in goodsList)
            {
                comboBoxGoods.Items.Add(good.Name);
            }
        }
        private void FormPurchaseManagement_Load(object sender, EventArgs e)
        {
            // 加载供应商数据到供应商下拉框
            LoadSuppliersData();

            // 加载商品数据到商品下拉框
            LoadGoodsData();

            // 设置采购日期选择器的初始值（这里设置为当天日期，可根据需求调整）
            dateTimePickerPurchaseDate.Value = DateTime.Today;

            // 例如可以设置采购数量输入框等的默认提示文本（以下为示例，可按需扩展完善）
            textBoxQuantity.Text = "请输入采购数量";
            textBoxQuantity.ForeColor = Color.Gray;
            textBoxQuantity.GotFocus += (s, args) =>
            {
                if (textBoxQuantity.Text == "请输入采购数量")
                {
                    textBoxQuantity.Text = "";
                    textBoxQuantity.ForeColor = Color.Black;
                }
            };
            textBoxQuantity.LostFocus += (s, args) =>
            {
                if (string.IsNullOrEmpty(textBoxQuantity.Text))
                {
                    textBoxQuantity.Text = "请输入采购数量";
                    textBoxQuantity.ForeColor = Color.Gray;
                }
            };
        }

        private DateTimePicker dateTimePickerPurchaseDate;
        private ComboBox comboBoxGoods;
        private ComboBox comboBoxSuppliers;
        private TextBox textBoxQuantity;
        private TextBox textBoxUnitPrice;
        private TextBox textBoxTotalAmount;
        private DataGridView dataGridViewPurchases;
    }


}

namespace SnacksInventorySystem
{
    partial class FormPurchaseManagement
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要修改
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(600, 400);
            this.IsMdiContainer = false;
            this.MainMenuStrip = null;
            this.Name = "FormPurchaseManagement";
            this.Text = "采购管理";
            this.Load += new System.EventHandler(this.FormPurchaseManagement_Load);
        }

        #endregion
    }
}