using System;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;
using System.Drawing;
namespace SnacksInventorySystem
{
public partial class FormQueryStatistics : Form
{
public FormQueryStatistics()
{
InitializeComponent();
InitializeUI();
}
private void InitializeUI()
{
// 设置窗口标题
Text = "查询统计";
// 设置窗口大小
Width = 600;
Height = 400;
// 设置窗口起始位置为屏幕中央
StartPosition = FormStartPosition.CenterScreen;
// 创建开始日期选择器
dateTimePickerStartDate = new DateTimePicker();
dateTimePickerStartDate.Location = new Point(20, 20);
dateTimePickerStartDate.Width = 150;
// 创建结束日期选择器
dateTimePickerEndDate = new DateTimePicker();
dateTimePickerEndDate.Location = new Point(200, 20);
dateTimePickerEndDate.Width = 150;
// 创建查询类型下拉框（示例可查询销售、采购等不同类型数据，可拓展更多）
comboBoxQueryType = new ComboBox();
comboBoxQueryType.Items.AddRange(new object[] { "销售总额统计", "采购总额统计", "按商品分类统计销量" });
comboBoxQueryType.Location = new Point(380, 20);
comboBoxQueryType.Width = 150;
comboBoxQueryType.SelectedIndex = 0;
// 创建查询按钮
Button buttonQuery = new Button();
buttonQuery.Text = "查询";
buttonQuery.Location = new Point(20, 60);
buttonQuery.Click += buttonQuery_Click;
// 创建用于显示查询统计结果的文本框（可根据实际展示需求换为更合适控件，如DataGridView等）
textBoxResult = new TextBox();
textBoxResult.Location = new Point(20, 100);
textBoxResult.Width = 560;
textBoxResult.Height = 250;
textBoxResult.Multiline = true;
textBoxResult.ReadOnly = true;
// 将各控件添加到查询统计窗口
Controls.Add(dateTimePickerStartDate);
Controls.Add(dateTimePickerEndDate);
Controls.Add(comboBoxQueryType);
Controls.Add(buttonQuery);
Controls.Add(textBoxResult);
}
private void buttonQuery_Click(object sender, EventArgs e)
{
DateTime startDate = dateTimePickerStartDate.Value;
DateTime endDate = dateTimePickerEndDate.Value;
string queryType = comboBoxQueryType.SelectedItem.ToString();
if (startDate > endDate)
{
MessageBox.Show("开始日期不能大于结束日期，请重新选择！");
return;
}
string connectionString = "Data Source=LAPTOP-ODQTAPDG\\MSSQLSERVER01;Initial Catalog=SnacksInventoryDB;User ID=root;Password=123456";
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = "";
switch (queryType)
{
case "销售总额统计":
query = "SELECT SUM(total_amount) AS TotalSales FROM sales WHERE sales_date BETWEEN @StartDate AND @EndDate";
break;
case "采购总额统计":
query = "SELECT SUM(total_amount) AS TotalPurchases FROM purchase WHERE purchase_date BETWEEN @StartDate AND @EndDate";
break;
case "按商品分类统计销量":
query = @"SELECT g.category, SUM(s.quantity) AS TotalQuantity
FROM sales s
JOIN goods g ON s.goods_id = g.goods_id
WHERE s.sales_date BETWEEN @StartDate AND @EndDate
GROUP BY g.category";
break;
default:
MessageBox.Show("暂不支持的查询类型，请选择正确的类型！");
return;
}
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@StartDate", startDate);
command.Parameters.AddWithValue("@EndDate", endDate);
try
{
connection.Open();
object result = command.ExecuteScalar();
if (result != null)
{
switch (queryType)
{
case "销售总额统计":
textBoxResult.Text = $"在 {startDate.ToShortDateString()} 至 {endDate.ToShortDateString()} 期间的销售总额为：{result.ToString()}";
break;
case "采购总额统计":
textBoxResult.Text = $"在 {startDate.ToShortDateString()} 至 {endDate.ToShortDateString()} 期间的采购总额为：{result.ToString()}";
break;
case "按商品分类统计销量":
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dataTable);
textBoxResult.Text = "按商品分类统计销量如下：\n";
foreach (DataRow row in dataTable.Rows)
{
textBoxResult.Text += $"{row["category"]}: {row["TotalQuantity"]} 件\n";
}
break;
}
}
else
{
textBoxResult.Text = "未查询到符合条件的数据。";
}
}
catch (Exception ex)
{
MessageBox.Show("查询统计出错：" + ex.Message);
}
}
}
private void FormQueryStatistics_Load(object sender, EventArgs e)
{
// 设置日期选择器的初始值（例如设置开始日期为当月第一天，结束日期为当天，可根据需求调整）
dateTimePickerStartDate.Value = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
dateTimePickerEndDate.Value = DateTime.Today;
// 可以初始化查询类型下拉框的默认选中项（这里假设选择第一个选项作为默认）
comboBoxQueryType.SelectedIndex = 0;
// 若要预加载部分数据展示在界面上（比如最近一次常用查询结果等，此处简单示例加载固定数据）
textBoxResult.Text = "上次查询统计示例结果，仅供参考。";
// 或者在这里建立数据库连接，加载一些基础统计数据等更复杂操作（示例代码如下）
// string connectionString = "Data Source=YOUR_SERVER_NAME;Initial Catalog=SnacksInventoryDB;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD";
// using (SqlConnection connection = new SqlConnection(connectionString))
// {
// string query = "SELECT TOP 1 * FROM some_statistics_table"; // 替换为实际查询统计基础数据的语句
// SqlCommand command = new SqlCommand(query, connection);
// try
// {
// connection.Open();
// SqlDataReader reader = command.ExecuteReader();
// if (reader.Read())
// {
// // 根据读取到的数据设置界面显示内容，此处需按实际表结构和需求调整
// textBoxResult.Text = $"示例统计数据：{reader["column_name"].ToString()}";
// }
// reader.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show("预加载数据出错：" + ex.Message);
// }
// }
}
private DateTimePicker dateTimePickerStartDate;
private DateTimePicker dateTimePickerEndDate;
private ComboBox comboBoxQueryType;
private TextBox textBoxResult;
}
}
namespace SnacksInventorySystem
{
partial class FormQueryStatistics
{
/// <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 = "FormQueryStatistics";
this.Text = "查询统计";
this.Load += new System.EventHandler(this.FormQueryStatistics_Load);
}
#endregion
}
}
浙公网安备 33010602011771号