生成工资表
第一步，设计表结构，T_SalarySheet表和T_SalarySheetItem表如下：
第二部：创建这两个表对应的Model类，代码分别为：
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HRMSys.Model { public class SalarySheetItem { public Guid Id { get; set; } public Guid SheetId { get; set; } public Guid EmployeeId { get; set; } public decimal Bonus { get; set; } public decimal BaseSalary { get; set; } public decimal Fine { get; set; } public decimal Other { get; set; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HRMSys.Model { public class SalarySheet { public Guid Id { get; set; } public int Year { get; set; } public int Month { get; set; } public Guid DepartmentId { get; set; } } }
第三步：建立DAL类，建立SalarySheetDAL.cs，代码如下：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using HRMSys.Model; namespace HRMSys.DAL { public class SalarySheetDAL { /// <summary> /// 判断是否已经生成指定年月、部门的工资单 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="deptId"></param> /// <returns></returns> public bool IsExists(int year, int month, Guid deptId) { object obj = SqlHelper.ExecuteScalar(@"select count(*) from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId", new SqlParameter("@Year", year), new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); return Convert.ToInt32(obj) > 0; } /// <summary> /// 清理已经生成的工资单 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="deptId"></param> public void Clear(int year, int month, Guid deptId) { object obj = SqlHelper.ExecuteScalar(@"select Id from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId", new SqlParameter("@Year", year), new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); Guid sheetId = (Guid)obj; //一般先删明细表再删主表 SqlHelper.ExecuteNonQuery("delete from T_SalarySheetItem where SheetId=@SheetId", new SqlParameter("@SheetId", sheetId)); SqlHelper.ExecuteNonQuery("delete from T_SalarySheet where Id=@Id", new SqlParameter("@Id", sheetId)); } public void Build(int year, int month, Guid deptId) { //生成表头T_SalarySheet //查询部门的所有员工 //foreach(员工 in 员工们) //{针对每个员工都生成一条T_SalarySheetItem} //生成的时候是先生成主表，再生成明细表。因为明细表需要主表的Id Guid sheetId = Guid.NewGuid(); SqlHelper.ExecuteNonQuery(@"Insert into T_SalarySheet(Id,Year,Month,DepartmentId) Values(@Id,@Year,@Month,@DepartmentId)", new SqlParameter("@Id", sheetId), new SqlParameter("@Year", year), new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); Employee[] employees = new EmployeeDAL().ListByDepment(deptId); foreach (Employee employee in employees) { SqlHelper.ExecuteNonQuery(@"Insert into T_SalarySheetItem (Id,SheetId,EmployeeId,Bonus,BaseSalary,Fine,Other) values(newid(),@SheetId,@EmployeeId,0,0,0,0)", new SqlParameter("@SheetId",sheetId), new SqlParameter("@EmployeeId", employee.Id)); } } } }
其中还利用到SqlHelper.cs中的如下方法，来判断是否生成指定时间的工资单
public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } }
同时还用到EmployeeDAL.cs中的查询部门的所有员工代码如如下：
/// <summary> /// 查询部门下所有的员工 /// </summary> /// <param name="deptId"></param> /// <returns></returns> public Employee[] ListByDepment(Guid deptId) { DataTable table = SqlHelper.ExecuteDataTable(@"select * from T_Employee where DepartmentId=@DepartmentId", new SqlParameter("@DepartmentId", deptId)); return ToEmployees(table); }
在MainWindow.xaml 中添加如下代码：
MenuItem Name="miBuildSalarySheet" Header="生成工资单" Click="miBuildSalarySheet_Click"></MenuItem>
新建一个窗体BulidSalarySheetWindow窗体，设计和代码如下：
<Window x:Class="HRMSys.UI.BuildSalarySheetWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="BuildSalarySheetWindow" Height="300" Width="600" Loaded="Window_Loaded"> <Grid> <ComboBox Height="23" HorizontalAlignment="Left" Margin="16,10,0,0" Name="cmbYear" VerticalAlignment="Top" Width="100" /> <TextBlock Height="23" HorizontalAlignment="Left" Margin="122,12,0,0" Name="textBlock1" Text="年" VerticalAlignment="Top" /> <ComboBox Height="23" HorizontalAlignment="Left" Margin="140,10,0,0" Name="cmbMonth" VerticalAlignment="Top" Width="120" /> <TextBlock Height="25" HorizontalAlignment="Left" Margin="282,10,0,0" Name="textBlock2" Text="月" VerticalAlignment="Top" Width="19" /> <ComboBox Height="23" HorizontalAlignment="Left" Margin="320,12,0,0" Name="cmbDept" VerticalAlignment="Top" Width="120" DisplayMemberPath="Name" SelectedValuePath="Id" /> <Button Content="生成工资表" Height="23" HorizontalAlignment="Left" Margin="464,11,0,0" Name="btnCreateSalarySheet" VerticalAlignment="Top" Width="75" Click="btnCreateSalarySheet_Click" /> </Grid> </Window>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Shapes; using HRMSys.DAL; namespace HRMSys.UI { /// <summary> /// BuildSalarySheetWindow1.xaml 的交互逻辑 /// </summary> public partial class BuildSalarySheetWindow: Window { public BuildSalarySheetWindow() { InitializeComponent(); } private void Window_Loaded(object sender, RoutedEventArgs e) { List<int> listYears = new List<int>(); for (int i = DateTime.Now.Year - 5; i <= DateTime.Now.Year + 5; i++) { listYears.Add(i); } List<int> months = new List<int>(); for (int i = 1; i <= 12; i++) { months.Add(i); } cmbYear.ItemsSource = listYears; cmbMonth.ItemsSource = months; cmbYear.SelectedValue = DateTime.Now.Year; cmbMonth.SelectedValue = DateTime.Now.Month; cmbDept.ItemsSource = new DepartmentDAL().ListAll(); } private void btnCreateSalarySheet_Click(object sender, RoutedEventArgs e) { int year = (int)cmbYear.SelectedValue; int month = (int)cmbMonth.SelectedValue; Guid deptId = (Guid)cmbDept.SelectedValue; SalarySheetDAL dal = new SalarySheetDAL(); if (dal.IsExists(year, month, deptId)) { if (MessageBox.Show("工资单已经生成，是否重新生成？", "提示", MessageBoxButton.YesNo) == MessageBoxResult.Yes) { dal.Clear(year, month, deptId); } } dal.Build(year, month, deptId); MessageBox.Show("生成成功！"); } } }
以上完成，需要补充的如下：
