public SalarySheetItem[] GetItemsByDate(int year, int month, Guid dptid)
{
DataTable tableSheet = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheet
where Year = @year and Month = @month and DepartmentID = @dptid",
new SqlParameter("@Year", year),
new SqlParameter("@month", month),
new SqlParameter("@dptid", dptid));
if (tableSheet.Rows.Count == 1)
{
Guid SheetID = (Guid)tableSheet.Rows[0]["SalarySheetID"];
DataTable tableItem = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheetItem
where SalarySheetID = @SalarySheetItem",
new SqlParameter("@SalarySheetItem", SheetID));
SalarySheetItem[] Items = new SalarySheetItem[tableItem.Rows.Count];
for (int i = 0; i < tableItem.Rows.Count; i++)
{
Items[i] = ToModel(tableItem.Rows[i]);
}
return(Items);
}
else if (tableSheet.Rows.Count <= 0)
{
return(new SalarySheetItem[0]);
}
else
{
throw new Exception("");
}
}
private void datagridItems_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
//Binding="{Binding BaseSalary,UpdateSourceTrigger=PropertyChanged}"
SalarySheetItem item = (SalarySheetItem)e.Row.DataContext;
//e.Row.DataContext修改后的数据对象
new SalarySheetDAL().Update(item);
}
public void UpdateEmployeeSalarys(SalarySheetItem salaryitem)
{
SqlHelper.ExecuteNonQuery(@"update T_Employee set
BaseSalary = @BaseSalary, Bonus = @Bonus, Deduction = @Deduction, AfterTaxWage = @AfterTaxWage where ID =@ID",
new SqlParameter("@BaseSalary", SqlHelper.ToDBValue(salaryitem.BaseSalary)),
new SqlParameter("@Bonus", SqlHelper.ToDBValue(salaryitem.Bonus)),
new SqlParameter("@Deduction", SqlHelper.ToDBValue(salaryitem.Deduction)),
new SqlParameter("@AfterTaxWage", SqlHelper.ToDBValue(salaryitem.AfterTaxWage)),
new SqlParameter("@ID", salaryitem.EmployeeID));
}
public void Update(SalarySheetItem item)
{
SqlHelper.ExecuteNonQuery(@"Update T_SalarySheetItem
Set BaseSalary=@BaseSalary,Bonus=@Bonus,
Fine=@Fine,Other=@Other where Id=@Id",
new SqlParameter("@BaseSalary", item.BaseSalary),
new SqlParameter("@Bonus", item.Bonus),
new SqlParameter("@Fine", item.Fine),
new SqlParameter("@Other", item.Other),
new SqlParameter("@Id", item.Id));
}
public SalarySheetItem ToModel(DataRow row)
{
SalarySheetItem item = new SalarySheetItem();
item.SalarySheetItemID = (Guid)row["SalarySheetItemID"];
item.SalarySheetID = (Guid)row["SalarySheetID"];
item.EmployeeID = (Guid)row["EmployeeID"];
item.BaseSalary = (decimal)row["BaseSalary"];
item.Bonus = (decimal)row["Bonus"];
item.Deduction = (decimal)row["Deduction"];
item.AfterTaxWage = (decimal)row["AfterTaxWage"];
return(item);
}
public bool UpdateSalaryItems(SalarySheetItem item)
{
string sql = "update SalarySheetItem set BaseSalary = @BaseSalary, Bonus = @Bonus, Fine = @Fine, Other = @Other where EmployeeId = @Id";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("Id", item.Id),
new SqlParameter("BaseSalary", item.BaseSalary),
new SqlParameter("Bonus", item.Bonus),
new SqlParameter("Fine", item.Fine),
new SqlParameter("Other", item.Other)
};
return(SqlHelper.ExcuteNonQuery(sql, parameters) > 0);
}
private void btnConfirm_Click(object sender, EventArgs e)
{
SalarySheetItem item = null;
for (int i = 0; i < DataGridView.Rows.Count - 1; i++)
{
item = new SalarySheetItem();
item.Id = (Guid)DataGridView.Rows[i].Cells[0].Value;
item.BaseSalary = (decimal)DataGridView.Rows[i].Cells[2].Value;
item.Bonus = (decimal)DataGridView.Rows[i].Cells[3].Value;
item.Fine = (decimal)DataGridView.Rows[i].Cells[4].Value;
item.Other = (decimal)DataGridView.Rows[i].Cells[5].Value;
ss.UpdateSalaryItems(item);
}
CommonHelper.SuccessReply("工资单保存成功!");
}
public void Update(SalarySheetItem item)
{
SqlHelper.ExecuteNonQuery(@"Update T_SalarySheetItem set
BaseSalary = @BaseSalary,
Bonus = @Bonus,
Deduction = @Deduction,
AfterTaxWage = @AfterTaxWage
where SalarySheetItemID = @SalarySheetItemID",
new SqlParameter("@BaseSalary", SqlHelper.ToDBValue(item.BaseSalary)),
new SqlParameter("@Bonus", SqlHelper.ToDBValue(item.Bonus)),
new SqlParameter("@Deduction", SqlHelper.ToDBValue(item.Deduction)),
new SqlParameter("@AfterTaxWage", SqlHelper.ToDBValue(item.AfterTaxWage)),
new SqlParameter("@SalarySheetItemID", item.SalarySheetItemID));
EmployeeDAL employDAL = new EmployeeDAL();
employDAL.UpdateEmployeeSalarys(item);
}
public SalarySheetItem[] GetSalarySheetItems(int year, int month, Guid deptId)
{
DataTable tableMain = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheet
where Year=@Year and Month=@Month and DepartmentId=@DepartmentId",
new SqlParameter("@Year", year),
new SqlParameter("@Month", month),
new SqlParameter("@DepartmentId", deptId));
//先查询指定年月、部门的工资数据主表Id。再查询子表信息
//todo:可以使用“子查询”技术来简化
if (tableMain.Rows.Count == 1)
{
Guid sheetId = (Guid)tableMain.Rows[0]["Id"];
DataTable table = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheetItem where
SheetId=@SheetId",
new SqlParameter("@SheetId", sheetId));
SalarySheetItem[] items = new SalarySheetItem[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
SalarySheetItem item = new SalarySheetItem();
item.Id = (Guid)row["Id"];
item.BaseSalary = (decimal)row["BaseSalary"];
item.Bonus = (decimal)row["Bonus"];
item.Fine = (decimal)row["Fine"];
item.Other = (decimal)row["Other"];
item.EmployeeId = (Guid)row["EmployeeId"];
item.SheetId = (Guid)row["SheetId"];
items[i] = item;
}
return(items);
}
else if (tableMain.Rows.Count <= 0)
{
return(new SalarySheetItem[0]);
}
else
{
throw new Exception();
}
}
private void datagrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
SalarySheetItem item = (SalarySheetItem)e.Row.DataContext;
Guid EmployeeID = item.EmployeeID;
EmployeeDAL employdal = new EmployeeDAL();
Employee employee = employdal.GetEmployeeByID(EmployeeID);
BaseSalaryRangeDAL BsrDAL = new BaseSalaryRangeDAL();
decimal uperLimit = new BaseSalaryRangeDAL().GetBaseSalaryUperLimitByRankID(employee);
decimal lowerLimit = new BaseSalaryRangeDAL().GetBaseSalaryLowerLimitByRankID(employee);
decimal basesalary = item.BaseSalary;
if (basesalary <= uperLimit && basesalary >= lowerLimit)
{
new SalarySheetDAL().Update(item);
}
else
{
MessageBox.Show("BaseSalary is out of range!");
return;
}
}
private void btnQuery_Click(object sender, RoutedEventArgs e)
{
int year = Convert.ToInt32(txtYear.Text);
int month = Convert.ToInt32(txtMonth.Text);
Guid deptId = (Guid)cmbDepts.SelectedValue;
SalarySheetDAL sheetDAL = new SalarySheetDAL();
if (sheetDAL.IsExists(year, month, deptId) == false)
{
MessageBox.Show("还未生成工资！");
return;
}
SalarySheetItem[] items =
sheetDAL.GetSalarySheetItems(year, month, deptId);
SalarySheetItemRpt[] rptItems = new SalarySheetItemRpt[items.Length];
for (int i = 0; i < items.Length; i++)
{
SalarySheetItem item = items[i];
SalarySheetItemRpt rptItem = new SalarySheetItemRpt();
rptItem.BaseSalary = item.BaseSalary;
rptItem.Bonus = item.Bonus;
rptItem.Fine = item.Fine;
rptItem.Other = item.Other;
//select emp.Name join T_employee
rptItem.EmployeeName =
new EmployeeDAL().GetById(item.EmployeeId).Name;
rptItems[i] = rptItem;
}
SalarySheetReport report = new SalarySheetReport();
report.SetDataSource(rptItems);
report.SetParameterValue("年", year);
report.SetParameterValue("月", month);
report.SetParameterValue("部门名称", cmbDepts.Text);
reportsViewerItems.ViewerCore.ReportSource = report;
}
private void btnQuery_Click(object sender, RoutedEventArgs e)
{
int year = Convert.ToInt32(txtYear.Text);
int month = Convert.ToInt32(txtMonth.Text);
Guid DeptId = (Guid)cmbDept.SelectedValue;
if (SalarySheetDAL.IsExist(year, month, DeptId) == false)
{
MessageBox.Show("还没有生成工资表！");
return;
}
SalarySheetItem[] salarySheetItems = SalarySheetDAL.GetSalarySheetItem(year, month, DeptId);
SalarySheetItemrpt[] salarySheetItemrpts = new SalarySheetItemrpt[salarySheetItems.Length];
for (int i = 0; i < salarySheetItems.Length; i++)
{
SalarySheetItem item = salarySheetItems[i];
SalarySheetItemrpt itemRpt = new SalarySheetItemrpt();
itemRpt.BaseSalary = item.BaseSalary;
itemRpt.Bonus = item.Bonus;
itemRpt.Fine = item.Fine;
itemRpt.Other = item.Other;
itemRpt.EmployeeName = EmployeeDAL.GetById(item.EmployeeId).Name;
salarySheetItemrpts[i] = itemRpt;
}
SalarySheetItemCrystalReport rpt = new SalarySheetItemCrystalReport();
rpt.SetDataSource(salarySheetItemrpts);
rpt.SetParameterValue("年", year);
rpt.SetParameterValue("月", month);
rpt.SetParameterValue("部门", cmbDept.Text);
crystalReportsViewer1.ViewerCore.ReportSource = rpt;
}
private void dataGridSalaryItems_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
SalarySheetItem item = (SalarySheetItem)e.Row.DataContext;
SalarySheetDAL.UpdateSalarySheet(item);
}
