private void btnAudit2_Click(object sender, EventArgs e)
{
if (this.dtgList.SelectedRows.Count > 0)
{
var oModel = new PayOffLogic();
if (MessageBox.Show("确定要审批通过吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
foreach (DataGridViewRow dgvr in this.dtgList.SelectedRows)
{
oModel.Checkup(long.Parse(dgvr.Cells["id"].Value.ToString()), true, this.UserName);
}
}
else
{
foreach (DataGridViewRow dgvr in this.dtgList.SelectedRows)
{
oModel.Checkup(long.Parse(dgvr.Cells["id"].Value.ToString()), false, this.UserName);
}
}
this.btnSearch_Click(null, null);
}
else
{
CommUI.AlertError("请选择数据行!");
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
DataSet dtsResult = new PayOffLogic().GetPayAudit(this.datePayOff.Value.Year, this.datePayOff.Value.Month, this.cboPayoffName.Text.Trim());
dtgList.DataSource = dtsResult.Tables[0];
dtgList.Columns["id"].Visible = false;
dtgList.Columns[0].Frozen = true;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (this.dtsWillBeStore.Tables[0].Rows.Count <= 0)
{
return;
}
if (this.txtPayOffName.Text.Trim() == "")
{
CommUI.MsgOK("工资发放名称不能为空!");
this.txtPayOffName.Focus();
return;
}
PayOffLogic oExec = new PayOffLogic();
if (oExec.IsExistPayName(this.txtPayOffName.Text.Trim()))
{
CommUI.AlertError("工资发放名称己经存在,请修改!");
this.txtPayOffName.Focus();
return;
}
PayOffDTS dtsPayOff = new PayOffDTS();
foreach (DataRow oRow in this.dtsWillBeStore.Tables[0].Rows)
{
PayOffDTS.payOffGatherRow drPayOffGather = dtsPayOff.payOffGather.NewpayOffGatherRow();
drPayOffGather.piId = long.Parse(oRow["员工标识"].ToString());
drPayOffGather.realIncome = decimal.Parse(oRow["实领工资"].ToString());
drPayOffGather.month = this.datePayOff.Value;
drPayOffGather.inputDate = DateTime.Now;
drPayOffGather.cHumanName = oRow["员工姓名"].ToString();
drPayOffGather.inputMan = this.UserName;
drPayOffGather.name = this.txtPayOffName.Text.Trim();
dtsPayOff.payOffGather.AddpayOffGatherRow(drPayOffGather);
foreach (DataColumn oColumn in this.dtsWillBeStore.Tables[0].Columns)
{
// 判断每一列是否为薪水项
PayRollItem oPayRollItem = this.strHeader.Where(arg => arg.Name == oColumn.ColumnName).FirstOrDefault();
if (oPayRollItem != null)
{
PayOffDTS.payOffDetailRow drPayOffDetail = dtsPayOff.payOffDetail.NewpayOffDetailRow();
drPayOffDetail.paid = oPayRollItem.ID;
drPayOffDetail.piId = drPayOffGather.piId;
drPayOffDetail.money = oRow[oColumn].ToString() == "" ? 0m : decimal.Parse(oRow[oColumn].ToString());
drPayOffDetail.month = drPayOffGather.month.Month;
drPayOffDetail.inputMan = this.UserName;
dtsPayOff.payOffDetail.AddpayOffDetailRow(drPayOffDetail);
}
}
}
oExec.AddPayOff(dtsPayOff);
CommUI.MsgOK();
this.dtsWillBeStore.Tables[0].Rows.Clear();
}
private void btnSearch_Click(object sender, EventArgs e)
{
DataSet dtsResult = new PayOffLogic().GetPay(this.datePayOff.Value.Year, this.datePayOff.Value.Month, this.txtPayoffName.Text.Trim());
dtgList.DataSource = dtsResult.Tables[0];
}
/// <summary>
/// 查询指定年月,工资发放名称的全部员工工资(包括未审批的)
/// </summary>
/// <param name="iYear">年</param>
/// <param name="iMonth">月</param>
/// <param name="strPayoffName">工资发放名称</param>
/// <returns></returns>
public System.Data.DataSet GetPayAudit(int iYear, int iMonth, string strPayoffName)
{
try
{
System.Data.DataSet dtsResult = new PayOffDB().QueryPayAudit(iYear, iMonth, strPayoffName);
DataSet dtsDataSource = new DataSet();
dtsDataSource.Tables.Add(new DataTable());
PayRollItem[] oPayRollItems = new PayOffLogic().GetPayRollItems();
//生成全部工资项列
dtsDataSource.Tables[0].Columns.Add("员工姓名");
foreach (PayRollItem item in oPayRollItems)
{
dtsDataSource.Tables[0].Columns.Add(new DataColumn(item.Name));
}
dtsDataSource.Tables[0].Columns.Add("实际所得");
dtsDataSource.Tables[0].Columns.Add("id");
dtsDataSource.Tables[0].Columns.Add("审核状态");
dtsDataSource.Tables[0].Columns.Add("审批状态");
var result = (from DataRow row in dtsResult.Tables[0].Rows
group row by row["cHumanName"] into Humans
join DataRow row in dtsResult.Tables[0].Rows on Humans.Key.ToString() equals row["cHumanName"].ToString() into RollItems
select new
{
ID = Humans.First()["id"],
HumanName = Humans.Key,
Rows = from DataRow row in RollItems
from System.Data.DataColumn col in dtsResult.Tables[0].Columns
where col.ColumnName != "cHumanName" && col.ColumnName != "realInCome"
select new
{
ColName = row["cPayRollName"], // 列名称
ColValue = row["realInCome"] // 列值
},
RealyInCome = (from DataRow row in RollItems
from System.Data.DataColumn col in dtsResult.Tables[0].Columns
where col.ColumnName == "realInCome" && row["cPayRollName"].ToString() == "实际所得"
select row[col]).First()
, AssessState = Humans.First()["AssessState"]
,
checkupState = Humans.First()["checkupState"]
});
int iCount = 0;
foreach (var item in result)
{
dtsDataSource.Tables[0].Rows.Add(new string[dtsResult.Tables[0].Columns.Count]);
dtsDataSource.Tables[0].Rows[iCount]["员工姓名"] = item.HumanName;
foreach (DataColumn column in dtsDataSource.Tables[0].Columns)
{
var rollItem = item.Rows.Where(p => p.ColName.ToString() == column.ColumnName).FirstOrDefault();
if (rollItem != null)
{
dtsDataSource.Tables[0].Rows[iCount][column.ColumnName] = rollItem.ColValue;
}
}
dtsDataSource.Tables[0].Rows[iCount]["id"] = item.ID;
dtsDataSource.Tables[0].Rows[iCount]["审核状态"] = item.AssessState;
dtsDataSource.Tables[0].Rows[iCount]["审批状态"] = item.checkupState;
iCount++;
}
return(dtsDataSource);
}
catch (System.Data.SqlClient.SqlException)
{
return(null);
}
}
/*
* 方法名称：GetPay
* 方法功能描述：查询所有员工指定年月的工资
*
* 创建人：杨林
* 创建时间：2009-03-12
*
* 修改人：
* 修改时间：
* 修改内容：
*
*/
/// <summary>
/// 查询所有员工指定年月的工资
/// </summary>
/// <param name="iYear">年</param>
/// <param name="iMonth">月</param>
/// <returns>数据集</returns>
public System.Data.DataSet GetPay(int iYear, int iMonth, string strPayoffName)
{
try
{
System.Data.DataSet dtsResult = new PayOffDB().QueryPay(iYear, iMonth, strPayoffName);
DataSet dtsDataSource = new DataSet();
dtsDataSource.Tables.Add(new DataTable());
PayRollItem[] oPayRollItems = new PayOffLogic().GetPayRollItems();
//生成全部工资项列
dtsDataSource.Tables[0].Columns.Add("员工姓名");
foreach (PayRollItem item in oPayRollItems)
{
dtsDataSource.Tables[0].Columns.Add(new DataColumn(item.Name));
}
dtsDataSource.Tables[0].Columns.Add("实际所得");
var result = (from DataRow row in dtsResult.Tables[0].Rows
group row by row["cHumanName"] into Humans // 获取全部人员工资列表,按照人员名称进行分组,因为结果集中单个人员对应多个工资项;
// 将分组结果连接结果集中的所有行数据,条件为人员名称相同的记录,结果存储在变量RollItems中.
join DataRow row in dtsResult.Tables[0].Rows on Humans.Key.ToString() equals row["cHumanName"].ToString() into RollItems
// 映射投影,建立一个匿名对象,三个属性分别为[人员名称],[该人员的工资项集合{工资项名称,工资项值}],[实际所得].
select new
{
HumanName = Humans.Key,
// 工资项匿名对象集合,用来表示每个员工的工资项.
// 联合前面分组后的结果集与数据列.获取全部工资项.但从原始结果集中去掉了[人员名称]原始数据列.
Rows = from DataRow row in RollItems
from System.Data.DataColumn col in dtsResult.Tables[0].Columns
where col.ColumnName != "cHumanName" && col.ColumnName != "realInCome"
select new
{
ColName = row["cPayRollName"], // 列名称
ColValue = row["realInCome"] // 列值
},
RealyInCome = (from DataRow row in RollItems
from System.Data.DataColumn col in dtsResult.Tables[0].Columns
where col.ColumnName == "realInCome" && row["cPayRollName"].ToString() == "实际所得"
select row[col]).First() //该员工实际所得,去掉重项.
});
int iCount = 0;
//循环人员工资
foreach (var item in result)
{
//设置数据源DataSet列数
dtsDataSource.Tables[0].Rows.Add(new string[dtsResult.Tables[0].Columns.Count]);
//设置员工姓名
dtsDataSource.Tables[0].Rows[iCount]["员工姓名"] = item.HumanName;
//循环配置人员的列与数据源的列,将值付给数据源Dataset
foreach (DataColumn column in dtsDataSource.Tables[0].Columns)
{
var rollItem = item.Rows.Where(p => p.ColName.ToString() == column.ColumnName).FirstOrDefault();
//如果人员的列与数据源的列匹配
if (rollItem != null)
{
dtsDataSource.Tables[0].Rows[iCount][column.ColumnName] = rollItem.ColValue;
}
}
iCount++;
}
return(dtsDataSource);
#region 原数据结构
//黎明 基本工资 2777.00 3226.7798
//黎明 项目奖金 1000.00 3226.7798
//黎明 养老保险 181.296 3226.7798
//黎明 医疗保险 45.324 3226.7798
//黎明 失业保险 22.662 3226.7798
//黎明 医疗大额保险 0.00 3226.7798
//黎明 住房公积金 181.296 3226.7798
//黎明 扣税 109.64 3226.7798
//黎明 考勤 10.00 3226.7798
//夏阳明 基本工资 2700.00 2446.76
//夏阳明 项目奖金 100.00 2446.76
//夏阳明 养老保险 134.40 2446.76
//夏阳明 医疗保险 33.60 2446.76
//夏阳明 失业保险 16.80 2446.76
//夏阳明 医疗大额保险 0.00 2446.76
//夏阳明 住房公积金 134.40 2446.76
//夏阳明 扣税 24.04 2446.76
//夏阳明 考勤 10.00 2446.76
// 循环每人员工资行,item变量代表每个员工的工资记录行.
// 匿名对象结构图
//{
// HumanNmae = "",
// Rows = {
// ColName = "", //值类似为"cPayRollName"或者"mPayRollValue"
// ColValue="" //值类似为"养老保险"或者"189.00"
// },
// RealyInCome = ""
//}
//var result = (from DataRow row in dtsResult.Tables[0].Rows
// group row by row["cHumanName"] into Humans // 获取全部人员工资列表,按照人员名称进行分组,因为结果集中单个人员对应多个工资项;
// // 将分组集果连接结果集中的所有行数据,条件为人员名称相同的记录,结果存储在变量ABC中.
// join DataRow row in dtsResult.Tables[0].Rows on Humans.Key.ToString() equals row["cHumanName"].ToString() into ABC
// // 映射投影,建立一个匿名对象,三个属性分别为[人员名称],[该人员的工资项集合{工资项名称,工资项值}],[实际所得].
// select new
// {
// HumanName = Humans.Key,
// // 工资项匿名对象集合,用来表示每个员工的工资项.
// // 联合前面分组后的结果集与数据列.获取全部工资项.但从原始结果集中去掉了[人员名称]与[实际所得]原始数据列.
// Rows = from DataRow row in ABC
// from System.Data.DataColumn col in dtsResult.Tables[0].Columns
// where col.ColumnName != "cHumanName" && col.ColumnName != "realInCome"
// select new
// {
// ColName = col.ColumnName, // 列名称
// ColValue = row[col] // 列值
// },
// RealyInCome = (from DataRow row in ABC
// from System.Data.DataColumn col in dtsResult.Tables[0].Columns
// where col.ColumnName == "realInCome"
// select row[col]).First() //该员工实际所得,去掉重项.
// });//.Distinct();//仅返回结果集中的非重复人员.
//dtsDataSource.Tables[0].Columns.Add("员工姓名");
//dtsDataSource.Tables[0].Columns.Add("实际所得");
//foreach (var item in result)
//{
// // 如果数据源表没有列,则产生列名.
// //if (dtsDataSource.Tables[0].Columns.Count == 0)
// //{
// var oColumns = item.Rows.Where(Lambda => Lambda.ColName == "cPayRollName");
// var temp = (from DataColumn t in dtsDataSource.Tables[0].Columns
// select t).ToList();
// foreach (var col in oColumns)
// {
// if(temp.Where(p=>p.ColumnName == col.ColValue.ToString()).Count() ==0)
// {
// dtsDataSource.Tables[0].Columns.Add(new DataColumn(col.ColValue.ToString()));
// }
// }
// //}
// // 确定列数,好添充列值
// string[] strValues = new string[dtsDataSource.Tables[0].Columns.Count];
// strValues[0] = item.HumanName.ToString();
// strValues[1] = item.RealyInCome.ToString();
// // 从该员工的工资项集中获取工资项的金额对象数组
// var colValues = item.Rows.Where(lambda => lambda.ColName == "mPayRollValue").ToList();
// // 将工资项金额添充到数组中
// for (int i = 2, j = 0; i < strValues.Length; i++, j++)
// {
// strValues[i] = colValues[j].ColValue.ToString();
// }
// dtsDataSource.Tables[0].Rows.Add(strValues);
//}
//return dtsDataSource;
#endregion
}
catch (System.Data.SqlClient.SqlException)
{
return(null);
}
}