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();
}
}
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));
}
