using Miemie.New;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
namespace Miemie
{
public partial class MonthlyReportForm : Form
{
private DataTable monthlyData;
private readonly int currentUserId;
private bool showMonthlySummary = true; // 标记当前显示的是月汇总还是日汇总
public MonthlyReportForm(int currentUserId)
{
InitializeComponent();
this.currentUserId = currentUserId;
InitializeDataGridView();
InitializeControls();
Load += MonthlyReportForm_Load;
}
private void InitializeControls()
{
// 初始化切换按钮
btnToggleView.Text = "切换到日汇总";
btnToggleView.Click += BtnToggleView_Click;
// 初始化日历
monthCalendar1.MaxSelectionCount = 1;
monthCalendar1.DateSelected += MonthCalendar1_DateSelected;
// 修改查看详情按钮为打开记录表单
btnViewDetails.Text = "打开记录表单";
btnViewDetails.Click += BtnViewDetails_Click;
}
private void InitializeDataGridView()
{
dataGridView1.AutoGenerateColumns = false;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AllowUserToDeleteRows = false;
dataGridView1.ReadOnly = true;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
// 添加列
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
{
Name = "Date",
HeaderText = "日期",
DataPropertyName = "Date",
DefaultCellStyle = new DataGridViewCellStyle { Format = "yyyy-MM-dd" }
});
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
{
Name = "Income",
HeaderText = "收入",
DataPropertyName = "Income",
DefaultCellStyle = new DataGridViewCellStyle { Format = "C2", ForeColor = Color.Green }
});
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
{
Name = "Expense",
HeaderText = "支出",
DataPropertyName = "Expense",
DefaultCellStyle = new DataGridViewCellStyle { Format = "C2", ForeColor = Color.Red }
});
dataGridView1.Columns.Add(new DataGridViewTextBoxColumn
{
Name = "Balance",
HeaderText = "结余",
DataPropertyName = "Balance",
DefaultCellStyle = new DataGridViewCellStyle { Format = "C2" }
});
}
private void MonthlyReportForm_Load(object sender, EventArgs e)
{
LoadMonthlyData();
UpdateSummaryView();
}
private void LoadMonthlyData()
{
try
{
monthlyData = GetMonthlyDataFromDatabase(currentUserId);
}
catch (Exception ex)
{
MessageBox.Show($"加载数据失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private DataTable GetMonthlyDataFromDatabase(int userId)
{
DataTable data = new DataTable();
string connectionString = ConfigurationManager.ConnectionStrings["MiemieConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
string sql = @"
SELECT
RecordDate,
RecordType,
Amount,
CategoryName,
Description
FROM Records r
JOIN Categories c ON r.CategoryID = c.CategoryID
WHERE r.UserID = @UserId
AND RecordDate >= DATEADD(year, -1, GETDATE())"; // 查询一年内的数据
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@UserId", userId);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
adapter.Fill(data);
}
}
}
return data;
}
private void UpdateSummaryView()
{
if (monthlyData == null || monthlyData.Rows.Count == 0)
{
dataGridView1.DataSource = null;
return;
}
DataTable summaryTable = new DataTable();
if (showMonthlySummary)
{
// 按月汇总
summaryTable.Columns.Add("Date", typeof(DateTime));
summaryTable.Columns.Add("Income", typeof(decimal));
summaryTable.Columns.Add("Expense", typeof(decimal));
summaryTable.Columns.Add("Balance", typeof(decimal));
DataView dataView = new DataView(monthlyData);
var months = monthlyData.AsEnumerable()
.Select(r => new DateTime(r.Field<DateTime>("RecordDate").Year, r.Field<DateTime>("RecordDate").Month, 1))
.Distinct();
foreach (var month in months)
{
DataRow newRow = summaryTable.NewRow();
newRow["Date"] = month;
decimal income = monthlyData.AsEnumerable()
.Where(r => r.Field<DateTime>("RecordDate").Year == month.Year &&
r.Field<DateTime>("RecordDate").Month == month.Month &&
r.Field<string>("RecordType") == "Income")
.Sum(r => r.Field<decimal>("Amount"));
decimal expense = monthlyData.AsEnumerable()
.Where(r => r.Field<DateTime>("RecordDate").Year == month.Year &&
r.Field<DateTime>("RecordDate").Month == month.Month &&
r.Field<string>("RecordType") == "Expense")
.Sum(r => r.Field<decimal>("Amount"));
newRow["Income"] = income;
newRow["Expense"] = expense;
newRow["Balance"] = income - expense;
summaryTable.Rows.Add(newRow);
}
}
else
{
// 按日汇总
DateTime selectedMonth = monthCalendar1.SelectionStart;
summaryTable.Columns.Add("Date", typeof(DateTime));
summaryTable.Columns.Add("Income", typeof(decimal));
summaryTable.Columns.Add("Expense", typeof(decimal));
summaryTable.Columns.Add("Balance", typeof(decimal));
var daysInMonth = monthlyData.AsEnumerable()
.Where(r => r.Field<DateTime>("RecordDate").Year == selectedMonth.Year &&
r.Field<DateTime>("RecordDate").Month == selectedMonth.Month)
.Select(r => r.Field<DateTime>("RecordDate").Date)
.Distinct();
foreach (var day in daysInMonth)
{
DataRow newRow = summaryTable.NewRow();
newRow["Date"] = day;
decimal income = monthlyData.AsEnumerable()
.Where(r => r.Field<DateTime>("RecordDate").Date == day &&
r.Field<string>("RecordType") == "Income")
.Sum(r => r.Field<decimal>("Amount"));
decimal expense = monthlyData.AsEnumerable()
.Where(r => r.Field<DateTime>("RecordDate").Date == day &&
r.Field<string>("RecordType") == "Expense")
.Sum(r => r.Field<decimal>("Amount"));
newRow["Income"] = income;
newRow["Expense"] = expense;
newRow["Balance"] = income - expense;
summaryTable.Rows.Add(newRow);
}
}
dataGridView1.DataSource = summaryTable;
}
private void BtnToggleView_Click(object sender, EventArgs e)
{
showMonthlySummary = !showMonthlySummary;
btnToggleView.Text = showMonthlySummary ? "切换到日汇总" : "切换到月汇总";
UpdateSummaryView();
}
private void MonthCalendar1_DateSelected(object sender, DateRangeEventArgs e)
{
if (!showMonthlySummary)
{
UpdateSummaryView();
}
}
private void BtnViewDetails_Click(object sender, EventArgs e)
{
// 直接打开记录表单，不传递任何筛选条件
RecordsForm recordsForm = new RecordsForm(currentUserId);
recordsForm.ShowDialog();
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}
}其中MonthlyReportForm窗体点击btnViewDetails后不能跳转到RecordsForm查看详细记录（错误信息：弹出窗口：登陆失败），请修改MonthlyReportForm代码（不要新添加窗体），写出修改后完整代码（如果有其他窗体代码也需要修改，写出修改后完整代码）
最新发布
本文汇总了各种类型的数据库连接字符串，提供了一个全面的数据库连接资源站点链接。对于开发者来说，这是一个非常实用的参考资料，可以帮助快速查找并使用正确的数据库连接配置。
扫一扫
1397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
