/// <summary>
/// 导出到Excel中
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExportToExcel_Click(object sender, RoutedEventArgs e)
{
//保存
SaveFileDialog sdfExport = new SaveFileDialog();
sdfExport.Filter = "Excel文件|*.xls"; //定义文件格式
//判断是否保存
if (sdfExport.ShowDialog() != true)
{
return;
}
//导出的文件名
string filename = sdfExport.FileName;
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个表（sheet）
ISheet sheet = workbook.CreateSheet("我的收支信息");
//表头行
IRow rowHeader = sheet.CreateRow(0);
//创建单元格 （第0个 类型为字符串）
rowHeader.CreateCell(0, CellType.STRING).SetCellValue("项目");
rowHeader.CreateCell(1, CellType.STRING).SetCellValue("金额");
rowHeader.CreateCell(2, CellType.STRING).SetCellValue("收支");
rowHeader.CreateCell(3, CellType.STRING).SetCellValue("日期");
rowHeader.CreateCell(4, CellType.STRING).SetCellValue("备注");
//把查询结果导出到Excel
Account[] accounts = (Account[])dataGridShow.ItemsSource;
for (int i = 0; i < accounts.Length; i++)
{
Account account = accounts[i];
//表体 第二行开始
IRow row = sheet.CreateRow(i + 1);
// row.CreateCell(0, CellType.STRING).SetCellValue(account.Item);
IdName idname = new IdNameBLL().GetById(account.Item);//获得该对像的Item的ID 再查出名称
row.CreateCell(0, CellType.STRING).SetCellValue(idname.Name);
row.CreateCell(1, CellType.STRING).SetCellValue(account.Money);
idname = new IdNameBLL().GetById(account.CostType);
row.CreateCell(2, CellType.STRING).SetCellValue(idname.Name);
row.CreateCell(4, CellType.STRING).SetCellValue(account.Remarks);
//日期特殊处理
//用workbook创建CreateCellStyle，CreateDataFormat
ICellStyle styledate = workbook.CreateCellStyle(); //样式
IDataFormat format = workbook.CreateDataFormat(); //格式
//设置日期的显示格式
styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
//日期建为NUMERIC类型
ICell cellDate = row.CreateCell(3, CellType.NUMERIC);
cellDate.CellStyle = styledate;
cellDate.SetCellValue(account.Date);
}
//文件流写入
using (Stream stream = File.OpenWrite(filename))
{
workbook.Write(stream);
}
MessageBox.Show("导出成功！");
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
IdNameBLL idnamebll = new IdNameBLL();
Account account = new Account();
//获得用户的Id
account.OperatorId = LoginWindow.GetOperatorId();
// MessageBox.Show("执行了！");
//添加
if (IsAddNew)
{
//设置默认值
account.Date = DateTime.Now;
account.Money = 0;
account.Remarks = "无";
gridEditAccount.DataContext = account;
cbType.ItemsSource = idnamebll.GetByCategory("收支类型");
//cbType.SelectedIndex = 0;
}
//编辑
else
{
//拿出要编辑的对象的值
account = new AccountBLL().GetById(EditingId);
gridEditAccount.DataContext = account;
cbType.ItemsSource = idnamebll.GetByCategory("收支类型");
}
}
//查看支出
private void menuShowExpense_Click(object sender, RoutedEventArgs e)
{
dataGridShow.ItemsSource = null;
//获得操作用户的Id
Guid operatorId = LoginWindow.GetOperatorId();
//查询收入的Id，并赋值
IdName idname = new IdNameBLL().GetByName("支出");
new AccountBLL().GetByOperatorIdAndCostType(operatorId, idname.Id);
columnCostType.ItemsSource = new IdNameBLL().GetByCategory("收支类型");
//根据 登录的ID 和收的Id查询 操作者的支出
dataGridShow.ItemsSource = new AccountBLL().GetByOperatorIdAndCostType(operatorId, idname.Id);
}
//cbType 与cbItem的联动
private void cbType_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
IdNameBLL bll = new IdNameBLL();
if (cbType.SelectedIndex == 0)
{
cbItem.ItemsSource = bll.GetByCategory("收入类型");
}
else
{
cbItem.ItemsSource = bll.GetByCategory("支出类型");
}
}
//搜索coatType Item 联动
private void cmbSearchCostType_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
cmbSearchItem.IsEnabled = true;
IdNameBLL bll = new IdNameBLL();
if (cmbSearchCostType.SelectedIndex == 0)
{
cmbSearchItem.ItemsSource = bll.GetByCategory("收入类型");
}
else
{
cmbSearchItem.ItemsSource = bll.GetByCategory("支出类型");
}
}
//删除选中数据
private void btnDelete_Click(object sender, RoutedEventArgs e)
{
Account account = (Account)dataGridShow.SelectedItem;
//判断是否选中数据
if (account == null)
{
MessageBox.Show("请选择一条数据！");
return;
}
if (MessageBox.Show("确认删除此条数据吗？", "提醒", MessageBoxButton.YesNo) == MessageBoxResult.Yes)
{
IdName idname = new IdNameBLL().GetByName("收入");
int i; //deleted返回
bool isSuceess; //判断余额是否更新成功
using (TransactionScope ts = new TransactionScope())
{
i = new AccountBLL().DeleteById(account.Id);
Balance balance = new Balance();
if (idname.Id == account.CostType)//要删除的为收入
{
balance.Balances = (decimal)(-account.Money);
isSuceess = new BalanceBLL().Update(account.OperatorId, balance.Balances);
}
else
{
balance.Balances = (decimal)account.Money;
isSuceess = new BalanceBLL().Update(account.OperatorId, balance.Balances);
}
if (i > 0 && isSuceess)
{
MessageBox.Show("删除成功！");
}
else
{
MessageBox.Show("删除失败！");
}
ts.Complete();
}
LoadData();
}
}
//点击保存按钮的
private void btnSave_Click(object sender, RoutedEventArgs e)
{
Account account = (Account)gridEditAccount.DataContext;
AccountBLL accountBll = new AccountBLL();
//判断金额大于0
if (cbType.SelectedIndex > -1)
{
if (account.Money <= 0)
{
MessageBox.Show("你输入的收入金额不合法，请重新输入！");
return;
}
}
if (cbType.SelectedIndex < 0)
{
MessageBox.Show("请选择收支！");
return;
}
else if (cbItem.SelectedIndex < 0)
{
MessageBox.Show("请选择项目！");
return;
}
//有问题
else
{
//获得名字为收入的Id值
IdName idname = new IdNameBLL().GetByName("收入");
Balance balance = new Balance();
#region 添加收支
//添加收支
if (IsAddNew)
{
int i;
//分布式事务处理收支和余额，可实现回滚
using (TransactionScope ts = new TransactionScope())
{
i = accountBll.AddNew(account);
//判断是否为收入,收入为正的，支出为负数
if (account.CostType == idname.Id) //收入
{
balance.Balances = (decimal)account.Money;
}
else //支出
{
balance.Balances = (decimal)(-account.Money);
}
new BalanceBLL().Update(account.OperatorId, balance.Balances);
ts.Complete();//表示已完成
}
//严谨点Update也应该返回true or false
if (i < 0)
{
new OperationLogBLL().Insert(LoginWindow.GetOperatorId(), "添加收支，更新余额失败！");
MessageBox.Show("添加错误！");
}
else if (i > 1)
{
new OperationLogBLL().Insert(LoginWindow.GetOperatorId(), "添加收支时错误！");
throw new Exception("数据发生错误！");
}
else
{
new OperationLogBLL().Insert(LoginWindow.GetOperatorId(), "添加收支，更新余额成功！");//日志
MessageBox.Show("添加成功！");
this.Close();
}
}
#endregion
#region 编辑收支
//编辑收支
else
{
bool i; //插入余额是否成功
bool isAddbalanceSuc; //插入余额是否成功
//分布式处理
using (TransactionScope ts = new TransactionScope())
{
//更新收支
i = accountBll.Update(account);
//更新前的数据类型是否为收入，并计算出差额，
if (BeforeCostType == idname.Id)
{
//收入改为收入 只是金额的更改
if (account.CostType == idname.Id) //更改后收支类型，为收入
{
if ((decimal)account.Money != BeforeEditingMoney) //更新后的钱不等更新前的前
{
balance.Balances = (decimal)account.Money - BeforeEditingMoney; //加上前后的差额（后大于前取+，否则为—）
}
else
{
balance.Balances = 0;
}
}
//收入改为支出，前为收，后为支
else
{
// 先减去更新前的收入，再减去更新后的支出金额
balance.Balances = -BeforeEditingMoney - (decimal)account.Money;
}
}
//若更新前为支出，计算出差额
else
{
//判断更新后的类型
if (account.CostType == idname.Id) //更新后的类型为收入，更新前为支出
{
balance.Balances = BeforeEditingMoney + (decimal)account.Money; //先加上更新前的支出，再加上更新后的收入
}
//更新后为支出，更新前为支出
else
{
if (BeforeEditingMoney != (decimal)account.Money) //更新前的钱不等于更新后的钱，求钱差额
{
balance.Balances = BeforeEditingMoney - (decimal)account.Money; //更新前 减去更新后 若更新前大，那就加上差额
}
else
{
balance.Balances = 0;
}
}
}
isAddbalanceSuc = new BalanceBLL().Update(account.OperatorId, balance.Balances);
ts.Complete();
}
if (i && isAddbalanceSuc)
{
new OperationLogBLL().Insert(LoginWindow.GetOperatorId(), "编辑收支成功！");
MessageBox.Show("更改成功！");
this.Close();
}
else
{
new OperationLogBLL().Insert(LoginWindow.GetOperatorId(), "编辑收支失败！");
MessageBox.Show("更新失败！");
}
}
#endregion
}
}
