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