protected void btnImport_Click(object sender, System.Web.UI.ImageClickEventArgs e) { //获取数据 SalaryStandardModel searchModel = new SalaryStandardModel(); //设置查询条件 //岗位 searchModel.QuarterID = Request.Form["ddlSearchQuarter"].ToString(); //岗位职等 //string I= Request.Form.AllKeys.ToString(); searchModel.AdminLevel = Request.Form["ctSearchQuaterAdmin$ddlCodeType"].ToString(); //启用状态 searchModel.UsedStatus = Request.Form["ddlSearchUsedStatus"].ToString(); //查询数据 DataTable dtNotify = SalaryStandardBus.SearchSalaryStandardInfo(searchModel); //导出标题 string headerTitle = "岗位|岗位职等|工资项名称|金额|启用状态"; string[] header = headerTitle.Split('|'); //导出标题所对应的列字段名称 string columnFiled = "QuarterName|AdminLevelName|ItemName|UnitPrice|UsedStatusName"; string[] field = columnFiled.Split('|'); XBase.Common.OutputToExecl.ExportToTable(this.Page, dtNotify, header, field, "岗位工资设置"); }
private void Search() { SalaryStandardModel searchModel = new SalaryStandardModel(); //设置查询条件 //岗位 if (ddlDeptName.SelectedValue != "0") searchModel.QuarterID = ddlDeptName.SelectedValue; if (this.ddlStartMonth.SelectedValue != "0")//起始月份 searchModel.AdminLevel = ddlStartMonth.SelectedValue; if (this.ddlEndMonth.SelectedValue != "0")//结束月份 searchModel.AdminLevelName = ddlEndMonth.SelectedValue; if (this.ddlYear .SelectedValue != "0")//结束月份 searchModel.UnitPrice = ddlYear.SelectedValue; searchModel.UsedStatus = txtSearchEmployee.Value; UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //查询数据 DataTable dtNewTable = SalaryStandardBus.SearchSalaryDetailsReport(searchModel); DataTable dtData = new DataTable(); dtData.Columns.Add("WorkMoney");//部门 dtData.Columns.Add("CompanyCD");//月份 dtData.Columns.Add("EmployeeID");//人员编号 dtData.Columns.Add("ReprotNo");//人员姓名 dtData.Columns.Add("FixedMoney");//岗位 dtData.Columns.Add("TimeMoney");//职等 dtData.Columns.Add("AllGetMoney");//应发工资 dtData.Columns.Add("AllKillMoney");//应扣工资 dtData.Columns.Add("SalaryMoney");//实发工资 for (int i = 0; i < dtNewTable.Rows.Count; i++) { DataTable dt = SalaryStandardBus.GetEmployeeDetailsOutPiece(dtNewTable.Rows[i]["EmployeeID"] == null ? "" : dtNewTable.Rows[i]["EmployeeID"].ToString(), dtNewTable.Rows[i]["DeptID"] == null ? "" : dtNewTable.Rows[i]["DeptID"].ToString(), dtNewTable.Rows[i]["ReprotNo"] == null ? "" : dtNewTable.Rows[i]["ReprotNo"].ToString()); DataRow newRow = dtData.NewRow(); newRow["WorkMoney"] = dt.Rows[0]["DeptName"] == null ? "" : dt.Rows[0]["DeptName"].ToString(); newRow["CompanyCD"] = dtNewTable.Rows[i]["ReportMonth"] == null ? "" : dtNewTable.Rows[i]["ReportMonth"].ToString(); newRow["EmployeeID"] = dt.Rows[0]["EmployeeName"] == null ? "" : dt.Rows[0]["EmployeeName"].ToString(); newRow["FixedMoney"] = dt.Rows[0]["WorkMoney"] == null ? "" : dt.Rows[0]["WorkMoney"].ToString(); dtData.Rows.Add(newRow); } ReportDocument oRpt = new ReportDocument(); CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalaryWorkByPiece.rpt")); // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名 CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryReportSummary")); //查询数据 CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "起始年月:" + ddlYear.SelectedValue + "." + ddlStartMonth.SelectedValue + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "结束年月:" + ddlYear.SelectedValue + "." + ddlEndMonth.SelectedValue + "\""; CrystalReportSource1.ReportDocument.SetDataSource(dtData); CrystalReportSource1.DataBind(); // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像 CrystalReportViewer1.ReportSource = CrystalReportSource1; //CrystalReportViewer1.DataBind(); }
/// <summary> /// 查询工资标准信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchSalaryDetailsReport(SalaryStandardModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //执行查询 return SalaryStandardDBHelper.SearchSalaryDetailsReport(model); }
private void Search() { SalaryStandardModel searchModel = new SalaryStandardModel(); //设置查询条件 //岗位 if (ddlSearchQuarter.SelectedValue!="0") searchModel.QuarterID = ddlSearchQuarter.SelectedValue; //岗位职等 if (ddlSearchQuaterAdmin.SelectedValue != "0") searchModel.AdminLevel = ddlSearchQuaterAdmin.SelectedValue; //启用状态· searchModel.UsedStatus = "1"; UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //查询数据 DataTable dtData = SalaryStandardBus.SearchSalaryStandardReport(searchModel); ReportDocument oRpt = new ReportDocument(); CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalaryStandardReport.rpt")); // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名 CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryStandard")); //查询数据 CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "" + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "" + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\""; if (ddlSearchQuarter.SelectedValue != "0" && ddlSearchQuarter.SelectedIndex != -1) { CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "岗位:" + ddlSearchQuarter.Items[ddlSearchQuarter.SelectedIndex].Text + "\""; } else { CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "岗位:" + "全部" + "\""; } if (this.ddlSearchQuaterAdmin.SelectedValue != "0" && ddlSearchQuaterAdmin.SelectedIndex != -1) { CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "职等:" + ddlSearchQuaterAdmin.Items[ddlSearchQuaterAdmin.SelectedIndex].Text + "\""; } else { CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "职等:" + "全部" + "\""; } CrystalReportSource1.ReportDocument.SetDataSource(dtData); CrystalReportSource1.DataBind(); // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像 CrystalReportViewer1.ReportSource = CrystalReportSource1; // CrystalReportViewer1.DataBind(); }
private void Search() { SalaryStandardModel searchModel = new SalaryStandardModel(); //设置查询条件 //岗位 if (ddlDeptName.SelectedValue != "0")//部门 searchModel.QuarterID = ddlDeptName.SelectedValue; if (this.ddlStartMonth .SelectedValue != "0")//起始月份 searchModel.AdminLevel = ddlStartMonth.SelectedValue; if (this.ddlEndMonth .SelectedValue != "0")//结束月份 searchModel.AdminLevelName = ddlEndMonth.SelectedValue; if (this.ddlYear .SelectedValue != "0")//结束月份 searchModel.UnitPrice = ddlYear.SelectedValue; UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //查询数据 DataTable dtNewTable= SalaryStandardBus.SearchSalarySummaryReport(searchModel); DataTable dtData = new DataTable(); dtData.Columns.Add("Remark"); dtData.Columns.Add("itemNo"); dtData.Columns.Add("CompanyCD"); dtData.Columns.Add("UnitPrice"); for (int i = 0; i < dtNewTable.Rows.Count; i++) { DataRow newRow = dtData.NewRow(); newRow["Remark"] = getDeptName(dtNewTable.Rows[i]["Remark"] == null ? "" : dtNewTable.Rows[i]["Remark"].ToString ()); newRow["itemNo"] = dtNewTable.Rows[i]["itemNo"] == null ? "" : dtNewTable.Rows[i]["itemNo"].ToString(); newRow["CompanyCD"] = dtNewTable.Rows[i]["CompanyCD"] == null ? "" : dtNewTable.Rows[i]["CompanyCD"].ToString(); newRow["UnitPrice"] = dtNewTable.Rows[i]["UnitPrice"] == null ? "" : dtNewTable.Rows[i]["UnitPrice"].ToString(); dtData.Rows.Add(newRow); } ReportDocument oRpt = new ReportDocument(); CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/SalarySummeryReport.rpt")); // SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息,参数一:用户名;参数二:密码;参数三:服务器;参数四:数据库名 CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.SalaryReportSummary")); //查询数据 CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "起始年月:" + ddlYear.SelectedValue + "." + ddlStartMonth.SelectedValue + "\""; CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "结束年月:" + ddlYear.SelectedValue + "." + ddlEndMonth.SelectedValue + "\""; CrystalReportSource1.ReportDocument.SetDataSource(dtData); CrystalReportSource1.DataBind(); // CrystalReportViewer1是水晶报表浏览器,下面是给该浏览器赋上对像 CrystalReportViewer1.ReportSource = CrystalReportSource1; //CrystalReportViewer1.DataBind(); }
/// <summary> /// 查询工资标准信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static bool CheckSalaryStandardInfo(SalaryStandardModel model) { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT "); searchSql.AppendLine(" ID "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" officedba.SalaryStandard "); searchSql.AppendLine(" WHERE "); searchSql.AppendLine(" CompanyCD = @CompanyCD "); searchSql.AppendLine(" AND ItemNo = @ItemNo "); searchSql.AppendLine(" AND QuarterID = @QuarterID "); searchSql.AppendLine(" AND AdminLevel = @AdminLevel "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); #region 设置参数 //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //岗位ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID)); //岗位职等 comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel)); //工资编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", model.ItemNo)); #endregion //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 DataTable dtStandard = SqlHelper.ExecuteSearch(comm); if (dtStandard == null || dtStandard.Rows.Count < 1) return false; else return true; }
/// <summary> /// 计时工资走势分析明细页面 /// </summary> /// <param name="CompanyCD"></param> /// <param name="DeptID"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable DeptTimeReportPrintSelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int totalCount) { return HumanReportDBHelper.DeptTimeReportPrintSelect(model, pageIndex, pageCount, ord, ref totalCount); }
/// <summary> /// 部门工资明细统计 /// </summary> /// <param name="CompanyCD"></param> /// <param name="DeptID"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable SalarySummerySelect(SalaryStandardModel searchModel, int pageIndex, int pageCount, string ord, ref int TotalCount) { return HumanReportDBHelper.SalarySummerySelect(searchModel, pageIndex, pageCount, ord, ref TotalCount); }
/// <summary> /// 更新工资标准信息 /// </summary> /// <param name="model">保存信息</param> /// <returns></returns> public static bool UpdateSalaryStandard(SalaryStandardModel model) { #region 更新SQL拼写 StringBuilder updateSql = new StringBuilder(); updateSql.AppendLine(" UPDATE officedba.SalaryStandard "); updateSql.AppendLine(" SET CompanyCD = @CompanyCD "); updateSql.AppendLine(" ,QuarterID = @QuarterID "); updateSql.AppendLine(" ,AdminLevel = @AdminLevel "); updateSql.AppendLine(" ,ItemNo = @ItemNo "); updateSql.AppendLine(" ,UnitPrice = @UnitPrice "); updateSql.AppendLine(" ,Remark = @Remark "); updateSql.AppendLine(" ,UsedStatus = @UsedStatus "); updateSql.AppendLine(" WHERE "); updateSql.AppendLine(" ID = @StandardID "); #endregion //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); comm.CommandText = updateSql.ToString(); //设置保存的参数 SetSaveParameter(comm, model); //ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@StandardID", model.ID)); //执行插入操作并返回更新结果 return SqlHelper.ExecuteTransWithCommand(comm); }
/// <summary> /// 工资走势月份分析明细页面 /// </summary> /// <param name="CompanyCD"></param> /// <param name="DeptID"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable DeptRealMoneyReportPrintSelect(SalaryStandardModel searchModel, int pageIndex, int pageCount, string ord, ref int totalCount) { try { SqlCommand comm = new SqlCommand(); StringBuilder Sql = new StringBuilder(); Sql.AppendLine(" select Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月' ReportMonth, isnull(B.SalaryMoney,0) as WorkMoney ,isnull( C.EmployeeName,'')as EmployeeName ,isnull(d.DeptName,'')as DeptName,B.EmployeeID from officedba.SalaryReport A left join officedba.SalaryReportSummary B"); Sql.AppendLine (" on A.ReprotNo =B.ReprotNo and A.companyCD=B.CompanyCD"); Sql.AppendLine (" left join officedba.employeeInfo C "); Sql.AppendLine (" on B.EmployeeID=C.ID"); Sql.AppendLine (" left outer join officedba.DeptInfo d "); Sql.AppendLine(" on d.companyCD=c.companyCD and c.DeptID=d.ID "); Sql.AppendLine(" where A.CompanyCD=@CompanyCD and A.Status=3 and B.SalaryMoney is not null and B.SalaryMoney>0"); if (!string.IsNullOrEmpty(searchModel.QuarterID)) { Sql.AppendLine(" AND deptID=@DeptID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", searchModel.QuarterID)); } if (!string.IsNullOrEmpty(searchModel.UnitPrice))//年度 { Sql.AppendLine(" AND Substring(a.ReportMonth, 1, 4)=@year"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@year", searchModel.UnitPrice)); } if (!string.IsNullOrEmpty(searchModel.AdminLevel))//年度 { Sql.AppendLine(" AND Substring(a.ReportMonth, 5, 2)=@month"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@month", searchModel.AdminLevel)); } comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", searchModel.CompanyCD )); //指定命令的SQL文 comm.CommandText = Sql.ToString(); //执行查询 //return SqlHelper.ExecuteSearch(comm); return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref totalCount); } catch (Exception ex) { string sss = ex.Message; return null; } }
/// <summary> /// 部门工资明细统计 /// </summary> /// <param name="CompanyCD"></param> /// <param name="DeptID"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable SalarySummerySelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int TotalCount) { try { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" select c.DeptID as Remark,Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月' as itemNo,count( distinct(b.EmployeeID)) as CompanyCD,sum(b.SalaryMoney) as UnitPrice "); searchSql.AppendLine(" from officedba.SalaryReport a left outer join officedba. SalaryReportSummary b "); searchSql.AppendLine(" on b.companyCD=a.companyCD and a.ReprotNo =b.ReprotNo "); searchSql.AppendLine(" left outer join officedba.EmployeeInfo c "); searchSql.AppendLine(" on c.companyCD=a.companyCD and b.EmployeeID=C.ID "); searchSql.AppendLine(" left outer join officedba.DeptInfo d "); searchSql.AppendLine(" on d.companyCD=a.companyCD and c.DeptID=d.ID "); searchSql.AppendLine(" where a. CompanyCD='"+model .CompanyCD +"' and a.Status='3' "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); #region 设置参数 //岗位ID if (!string.IsNullOrEmpty(model.QuarterID)) { searchSql.AppendLine(" AND c.DeptID ='" + model.QuarterID + "' "); } if (!string.IsNullOrEmpty(model.AdminLevel))//开始月份 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 5, 2)>='" + model.AdminLevel + "' "); } if (!string.IsNullOrEmpty(model.AdminLevelName))//结束月份 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 5, 2)<='" + model.AdminLevelName + "'"); } if (!string.IsNullOrEmpty(model.UnitPrice))//年度 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 1, 4)='" + model.UnitPrice + "'"); } #endregion searchSql.AppendLine(" group by c.DeptID,a.ReportMonth "); return SqlHelper.CreateSqlByPageExcuteSql(searchSql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount); } catch (Exception ex) { string sss = ex.Message; return null; } }
/// <summary> /// 工资标准明细 /// </summary> /// <param name="CompanyCD"></param> /// <param name="DeptID"></param> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> /// <param name="ord"></param> /// <param name="TotalCount"></param> /// <returns></returns> public static DataTable SalaryStandardSelect(SalaryStandardModel model, int pageIndex, int pageCount, string ord, ref int TotalCount) { try { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT "); searchSql.AppendLine(" ISNULL(A.QuarterID, '') AS QuarterID ,ISNULL(A.AdminLevel, '') AS AdminLevel ,ISNULL(D.ItemName, '') AS ItemName ,ISNULL(C.TypeName, '') AS TypeName ,sum(A.UnitPrice)AS UnitPrice , ISNULL( b.QuarterName, '') AS QuarterName "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" officedba.SalaryStandard A "); searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter B "); searchSql.AppendLine(" ON B.companyCD=A.companyCD AND B.ID = A.QuarterID "); searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType C "); searchSql.AppendLine(" ON C.companyCD=A.companyCD AND C.ID = A.AdminLevel "); searchSql.AppendLine(" LEFT JOIN officedba.SalaryItem D "); searchSql.AppendLine(" ON D.companyCD=A.companyCD AND D.ItemNo = A.ItemNo "); searchSql.AppendLine(" WHERE "); searchSql.AppendLine(" A.CompanyCD = '" + model.CompanyCD + "' "); #endregion //岗位ID if (!string.IsNullOrEmpty(model.QuarterID)) { searchSql.AppendLine(" AND A.QuarterID ='"+model.QuarterID+"' "); } //岗位职等 if (!string.IsNullOrEmpty(model.AdminLevel)) { searchSql.AppendLine(" AND A.AdminLevel = '"+model.AdminLevel+"' "); } //启用状态 if (!string.IsNullOrEmpty(model.UsedStatus)) { searchSql.AppendLine(" AND A.UsedStatus ='"+model.UsedStatus+"' "); } searchSql.AppendLine(" group by A.QuarterID,A.AdminLevel,A.ItemNo,D.ItemName ,C.TypeName,b.QuarterName"); return SqlHelper.CreateSqlByPageExcuteSql(searchSql.ToString(), pageIndex, pageCount, ord, null, ref TotalCount); } catch (Exception ex) { string sss = ex.Message; return null; } }
/// <summary> /// 新建工资标准信息 /// </summary> /// <param name="model">工资标准信息</param> /// <returns></returns> public static bool InsertSalaryStandard(SalaryStandardModel model) { #region 登陆SQL文 StringBuilder insertSql = new StringBuilder(); insertSql.AppendLine(" INSERT INTO "); insertSql.AppendLine(" officedba.SalaryStandard"); insertSql.AppendLine(" (CompanyCD "); insertSql.AppendLine(" ,QuarterID "); insertSql.AppendLine(" ,AdminLevel "); insertSql.AppendLine(" ,ItemNo "); insertSql.AppendLine(" ,UnitPrice "); insertSql.AppendLine(" ,Remark "); insertSql.AppendLine(" ,UsedStatus) "); insertSql.AppendLine(" VALUES "); insertSql.AppendLine(" (@CompanyCD "); insertSql.AppendLine(" ,@QuarterID "); insertSql.AppendLine(" ,@AdminLevel "); insertSql.AppendLine(" ,@ItemNo "); insertSql.AppendLine(" ,@UnitPrice "); insertSql.AppendLine(" ,@Remark "); insertSql.AppendLine(" ,@UsedStatus) "); insertSql.AppendLine(" SET @StandardID= @@IDENTITY "); #endregion //定义更新基本信息的命令 SqlCommand comm = new SqlCommand(); //设置存储过程名 comm.CommandText = insertSql.ToString(); //设置保存的参数 SetSaveParameter(comm, model); //添加返回参数 comm.Parameters.Add(SqlHelper.GetOutputParameter("@StandardID", SqlDbType.Int)); //执行登陆操作 bool isSucc = SqlHelper.ExecuteTransWithCommand(comm); //设置ID model.ID = comm.Parameters["@StandardID"].Value.ToString(); //执行插入并返回插入结果 return isSucc; }
/// <summary> /// 编辑工资标准信息 /// </summary> /// <param name="model">保存信息</param> /// <returns></returns> public static bool SaveSalaryStandard(SalaryStandardModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //定义返回变量 bool isSucc = false; //操作日志 LogInfoModel logModel = InitLogInfo(model.ID); //更新 if (!string.IsNullOrEmpty(model.ID)) { try { logModel.Element = ConstUtil.LOG_PROCESS_UPDATE; //执行更新操作 isSucc = SalaryStandardDBHelper.UpdateSalaryStandard(model); } catch (Exception ex) { //输出系统日志 WriteSystemLog(userInfo, ex); } } //插入 else { try { logModel.Element = ConstUtil.LOG_PROCESS_INSERT; //执行插入操作 isSucc = SalaryStandardDBHelper.InsertSalaryStandard(model); //操作单据编号 logModel.ObjectID = model.ID; } catch (Exception ex) { //输出系统日志 WriteSystemLog(userInfo, ex); } } //更新成功时 if (isSucc) { //设置操作成功标识 logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS; } //更新不成功 else { //设置操作成功标识 logModel.Remark = ConstUtil.LOG_PROCESS_FAILED; } //登陆日志 LogDBHelper.InsertLog(logModel); return isSucc; }
/// <summary> /// 校验工资标准信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static bool CheckSalaryStandardInfo(SalaryStandardModel model) { //获取登陆用户信息 UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //设置公司代码 model.CompanyCD = userInfo.CompanyCD; //执行查询 return SalaryStandardDBHelper.CheckSalaryStandardInfo(model); }
/// <summary> /// 查询工资标准信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchSalarySummaryReport(SalaryStandardModel model) { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" select c.DeptID as Remark,Substring(a.ReportMonth, 1, 4) + '年'+ Substring(a.ReportMonth, 5, 2) + '月' as itemNo,count( distinct(b.EmployeeID)) as CompanyCD,sum(b.SalaryMoney) as UnitPrice "); searchSql.AppendLine(" from officedba.SalaryReport a left outer join officedba. SalaryReportSummary b "); searchSql.AppendLine(" on b.companyCD=a.companyCD and a.ReprotNo =b.ReprotNo "); searchSql.AppendLine(" left outer join officedba.EmployeeInfo c "); searchSql.AppendLine(" on c.companyCD=a.companyCD and b.EmployeeID=C.ID "); searchSql.AppendLine(" left outer join officedba.DeptInfo d "); searchSql.AppendLine(" on d.companyCD=a.companyCD and c.DeptID=d.ID "); searchSql.AppendLine(" where a. CompanyCD=@CompanyCD and a.Status='3' "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); #region 设置参数 //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //岗位ID if (!string.IsNullOrEmpty(model.QuarterID)) { searchSql.AppendLine(" AND c.DeptID = @DeptID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.QuarterID)); } if (!string.IsNullOrEmpty(model.AdminLevel))//开始月份 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 5, 2)>=@StartMonth "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartMonth", model.AdminLevel)); } if (!string.IsNullOrEmpty(model.AdminLevelName))//结束月份 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 5, 2)<=@EndMonth"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndMonth", model.AdminLevelName)); } if (!string.IsNullOrEmpty(model.UnitPrice))//年度 { searchSql.AppendLine(" AND Substring(a.ReportMonth, 1, 4)=@year"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@year", model.UnitPrice)); } //岗位职等 //if (!string.IsNullOrEmpty(model.AdminLevel)) //{ // searchSql.AppendLine(" AND A.AdminLevel = @AdminLevel "); // comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel)); //} #endregion searchSql.AppendLine(" group by c.DeptID,a.ReportMonth "); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 查询工资标准信息 /// </summary> /// <param name="model">查询条件</param> /// <returns></returns> public static DataTable SearchSalaryStandardInfo(SalaryStandardModel model) { #region 查询语句 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine(" SELECT "); searchSql.AppendLine(" A.ID "); searchSql.AppendLine(" ,A.CompanyCD "); searchSql.AppendLine(" ,ISNULL(A.QuarterID, '') AS QuarterID "); searchSql.AppendLine(" ,ISNULL(B.QuarterName, '') AS QuarterName "); searchSql.AppendLine(" ,ISNULL(A.AdminLevel, '') AS AdminLevel "); searchSql.AppendLine(" ,ISNULL(C.TypeName, '') AS AdminLevelName "); searchSql.AppendLine(" ,ISNULL(A.ItemNo, '') AS ItemNo "); searchSql.AppendLine(" ,ISNULL(D.ItemName, '') AS ItemName "); searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR,A.UnitPrice), '') "); searchSql.AppendLine(" AS UnitPrice "); searchSql.AppendLine(" ,ISNULL(A.Remark, '') AS Remark "); searchSql.AppendLine(" ,ISNULL(A.UsedStatus, '') AS UsedStatus "); searchSql.AppendLine(" ,CASE A.UsedStatus "); searchSql.AppendLine(" WHEN '0' THEN '停用' "); searchSql.AppendLine(" WHEN '1' THEN '启用' "); searchSql.AppendLine(" ELSE '' "); searchSql.AppendLine(" END AS UsedStatusName "); searchSql.AppendLine(" FROM "); searchSql.AppendLine(" officedba.SalaryStandard A "); searchSql.AppendLine(" LEFT JOIN officedba.DeptQuarter B "); searchSql.AppendLine(" ON B.companyCD=A.companyCD AND B.ID = A.QuarterID "); searchSql.AppendLine(" LEFT JOIN officedba.CodePublicType C "); searchSql.AppendLine(" ON C.companyCD=A.companyCD AND C.ID = A.AdminLevel "); searchSql.AppendLine(" LEFT JOIN officedba.SalaryItem D "); searchSql.AppendLine(" ON D.companyCD=A.companyCD AND D.ItemNo = A.ItemNo "); searchSql.AppendLine(" WHERE "); searchSql.AppendLine(" A.CompanyCD = @CompanyCD "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); #region 设置参数 //公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //岗位ID if (!string.IsNullOrEmpty(model.QuarterID)) { searchSql.AppendLine(" AND A.QuarterID = @QuarterID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID)); } //岗位职等 if (!string.IsNullOrEmpty(model.AdminLevel)) { searchSql.AppendLine(" AND A.AdminLevel = @AdminLevel "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel)); } //启用状态 if (!string.IsNullOrEmpty(model.UsedStatus)) { searchSql.AppendLine(" AND A.UsedStatus = @UsedStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); } #endregion //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 保存时基本信息参数设置 /// </summary> /// <param name="comm">命令</param> /// <param name="model">保存信息</param> private static void SetSaveParameter(SqlCommand comm, SalaryStandardModel model) { //设置参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));//公司代码 comm.Parameters.Add(SqlHelper.GetParameterFromString("@QuarterID", model.QuarterID));//岗位ID comm.Parameters.Add(SqlHelper.GetParameterFromString("@AdminLevel", model.AdminLevel));//岗位职等 comm.Parameters.Add(SqlHelper.GetParameterFromString("@ItemNo", model.ItemNo));//工资项编号 comm.Parameters.Add(SqlHelper.GetParameterFromString("@UnitPrice", model.UnitPrice));//金额 comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", model.Remark));//备注 comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus));//启用状态 }