/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：TATModuleLogicDAL.cs * 2.功能描述：策略管理数据库访问类（插入、修改、删除） * 编辑履历： * 作者 日期 版本 修改内容 * 庄天威 2014/11/18 1.00 新建 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.IO; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Basics.Library; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.Service.CommonModuleLogic; using Oracle.DataAccess.Client; using System.Text; namespace Dongke.IBOSS.PRD.Service.TATModuleLogic { /// /// 策略管理数据库访问类（插入、修改、删除） /// public partial class TATModuleLogicDAL { #region 管理岗位工资策略 /// /// 新建管理岗位工资策略 /// /// 新建实体 /// 对应管理者明细 /// 当前用户信息 /// 服务返回实体 public static ServiceResultEntity AddManagerSalary(ManagerSalaryEntity msEntity, DataSet dsDetail, SUserInfo userInfo) { int RowsCount = 0; int CopyCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); StringBuilder sbSql = new StringBuilder(); //获取序列ID sbSql.Clear(); sbSql.Append("select SEQ_TAT_ManagerSalary_ID.nextval from dual"); int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); //添加管理岗位工资策略主体信息 sbSql.Append(@"Insert into TP_TAT_ManagerSalary (ManagerSalaryID,ManagerSalaryName,BeginAccountMonth,Remarks, AuditStatus,AccountID,CreateUserID,UpdateUserID) Values (:ManagerSalaryID,:ManagerSalaryName,:BeginAccountMonth,:Remarks, :AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, entityId,ParameterDirection.Input), new OracleParameter(":ManagerSalaryName",OracleDbType.NVarchar2, msEntity.ManagerSalaryName,ParameterDirection.Input), new OracleParameter(":BeginAccountMonth",OracleDbType.Date, msEntity.BeginAccountMonth,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, msEntity.Remarks,ParameterDirection.Input), new OracleParameter(":AuditStatus",OracleDbType.Int32, msEntity.AuditStatus,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), }; //连接数据库并返回结果 RowsCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); //正常添加的情况，要同时插入管理者信息 if (msEntity.CopyId == 0) { EditManagers(entityId, dsDetail, userInfo, oracleTrConn); } //如果是复制添加,那么还要将复制主体信息的明细全部添加 else { sbSql.Clear(); //首先获取要Copy的全部管理者信息查出 sbSql.Append("Select * from TP_TAT_Managers Where ManagerSalaryID=:MID"); OracleParameter[] MemberParas = new OracleParameter[]{ new OracleParameter(":MID",OracleDbType.Int32, msEntity.CopyId,ParameterDirection.Input) }; DataTable drManagers = oracleTrConn.GetSqlResultToDt(sbSql.ToString(), MemberParas); foreach(DataRow drFor in drManagers.Rows) { //录入需要Copy的管理者信息并获取ID //获取序列ID sbSql.Clear(); sbSql.Append("select SEQ_TAT_Managers_ManagersID.nextval from dual"); int managerId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); //插入被复制的信息 sbSql.Clear(); sbSql.Append(@"Insert into TP_TAT_Managers (ManagersID,ManagerSalaryID,Manager,SalaryType,SalaryPercent, AccountID,CreateUserID,UpdateUserID) Values (:ManagersID,:ManagerSalaryID,:Manager,:SalaryType,:SalaryPercent, :AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] CMParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, managerId,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, entityId,ParameterDirection.Input), new OracleParameter(":Manager",OracleDbType.Int32, drFor["Manager"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drFor["SalaryType"],ParameterDirection.Input), new OracleParameter(":SalaryPercent",OracleDbType.Decimal, drFor["SalaryPercent"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), }; CopyCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CMParas); //Copy明细 :ManagersID为新序列ID，ForManagersID为Copy信息ID sbSql.Clear(); sbSql.Append(@"Insert into TP_TAT_Members (ManagersID,ManagerSalaryID,Member,AccountID,CreateUserID,UpdateUserID) Select :ManagersID,:ManagerSalaryID,Member,AccountID,:CreateUserID,:UpdateUserID From TP_TAT_Members Where ManagersID=:ForManagersID"); OracleParameter[] CMemParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, managerId,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, entityId,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":ForManagersID",OracleDbType.Int32, drFor["ManagersID"],ParameterDirection.Input), }; CopyCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CMemParas); } } //如果插入失败则回滚事务并关闭 if (RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.ValidationFailed; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理岗位工资策略", "新建"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理岗位工资策略", "新建"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 编辑管理岗位工资策略 /// /// 策略实体 /// 明细数据源 /// 当前用户 /// 服务对象实体 public static ServiceResultEntity EditManagerSalary(ManagerSalaryEntity msEntity, DataSet dsDetail, SUserInfo userInfo) { int RowsCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Update TP_TAT_ManagerSalary Set ManagerSalaryName=:ManagerSalaryName, BeginAccountMonth=:BeginAccountMonth, Remarks=:Remarks, UpdateUserID=:UpdateUserID Where ManagerSalaryID=:ManagerSalaryID and OPTimeStamp=:OPTimeStamp"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryName",OracleDbType.NVarchar2, msEntity.ManagerSalaryName,ParameterDirection.Input), new OracleParameter(":BeginAccountMonth",OracleDbType.Date, msEntity.BeginAccountMonth,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, msEntity.Remarks,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, msEntity.ManagerSalaryID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, msEntity.OPTimeStamp,ParameterDirection.Input), }; //连接数据库并返回结果 RowsCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); //并更新对应工种明细 EditManagers(Convert.ToInt32(msEntity.ManagerSalaryID), dsDetail, userInfo, oracleTrConn); //如果插入失败则回滚事务并关闭 if (RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.DataChanged; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理岗位工资策略", "修改"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理岗位工资策略", "修改"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 审批管理岗位工资策略 /// /// 策略实体 /// 当前用户 /// /// 返回受影响行数 /// -999为策略开始月小于系统结算月, /// -998为存在同类别同开始日期策略. /// public static ServiceResultEntity AuditManagerSalary(ManagerSalaryEntity msEntity, SUserInfo userInfo) { int RowCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); //首先验证即将通过审批的实体的数据合法性 if (msEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree)) { RowCount = ValidationManagerSalary(oracleTrConn, msEntity, userInfo); if (RowCount != 0) { srEntity.Status = Constant.ServiceResultStatus.Other; srEntity.OtherStatus = RowCount; if (RowCount == -999) { srEntity.Message = "策略开始月不能小于系统结算月!"; } else if (RowCount == -998) { srEntity.Message = "已存在相同开始日期的该类型策略!"; } return srEntity; } } // 获得账务日期 DateTime accountDate = CommonModuleLogic.CommonModuleLogic.GetAccountDate(oracleTrConn, userInfo); //审批缺陷扣罚策略主体信息 StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Update TP_TAT_ManagerSalary Set AuditStatus=:AuditStatus, Auditor=:Auditor, AuditlDate=:AuditlDate Where ManagerSalaryID=:ManagerSalaryID And OPTimeStamp=:OPTimeStamp"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":AuditStatus",OracleDbType.Int32, msEntity.AuditStatus,ParameterDirection.Input), new OracleParameter(":Auditor",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AuditlDate",OracleDbType.Date, accountDate,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, msEntity.ManagerSalaryID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, msEntity.OPTimeStamp,ParameterDirection.Input), }; //连接数据库并返回结果 RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); //如果插入失败则回滚事务并关闭 if (RowCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.DataChanged; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理岗位工资策略", "审批"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理岗位工资策略", "审批"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 停用管理岗位工资策略 /// /// 停用实体 /// 当前用户信息 /// 返回影响行 public static ServiceResultEntity StopManagerSalary(ManagerSalaryEntity msEntity, SUserInfo userInfo) { int RowCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); // 获得账务日期 DateTime accountDate = CommonModuleLogic.CommonModuleLogic.GetAccountDate(oracleTrConn, userInfo); //停用管理岗位工资策略主体信息 StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Update TP_TAT_ManagerSalary Set ValueFlag = 0 Where ManagerSalaryID=:ManagerSalaryID And OPTimeStamp=:OPTimeStamp"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, msEntity.ManagerSalaryID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, msEntity.OPTimeStamp,ParameterDirection.Input), }; //连接数据库并返回结果 RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); if (RowCount == 0) //时间戳不对 { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.DataChanged; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理岗位工资策略", "停用"); return srEntity; } //停用管理岗位工资策略组员明细 sbSql.Clear(); sbSql.Append(@"Delete from TP_TAT_Members Where ManagerSalaryID = :ManagerSalaryID"); OracleParameter[] WDParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, msEntity.ManagerSalaryID,ParameterDirection.Input) }; RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WDParas); //停用管理岗位工资策略管理者明细 sbSql.Clear(); sbSql.Append(@"Delete from TP_TAT_Managers Where ManagerSalaryID = :ManagerSalaryID"); OracleParameter[] WGDParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, msEntity.ManagerSalaryID,ParameterDirection.Input) }; RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WGDParas); oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理岗位工资策略", "停用"); return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 验证数据有效性（是否可新建或编辑） /// /// 数据库事务连接 /// 当前操作实体 /// 当前用户 /// /// 0为可进行相应操作 /// -999为策略开始月小于系统结算月, /// -998为存在同类别同开始日期策略. /// public static int ValidationManagerSalary(IDBTransaction trConn, ManagerSalaryEntity msEntity, SUserInfo userInfo) { try { StringBuilder sbSql = new StringBuilder(); //条件一：策略开始月不得小于已结算月份(SystemDateType=5代表结算月日期) sbSql.Append(@"Select * from TP_MST_SystemDate Where SystemDateType = 5 and DateValue >= :BeginAccountMonth"); OracleParameter[] CSParas = new OracleParameter[] { new OracleParameter(":BeginAccountMonth",OracleDbType.Date, msEntity.BeginAccountMonth,ParameterDirection.Input) }; DataTable dtCheckSys = trConn.GetSqlResultToDt(sbSql.ToString(), CSParas); if (dtCheckSys.Rows.Count != 0) //如果当前结算月大于新建策略日期，False { return -999; } sbSql.Clear(); //条件二：不可存在同开始日期的已审批策略 sbSql.Append(@"Select * from TP_TAT_ManagerSalary Where BeginAccountMonth = :BeginAccountMonth and AccountID=:AccountID and AuditStatus = 1 and ValueFlag = 1"); OracleParameter[] CWParas = new OracleParameter[] { new OracleParameter(":BeginAccountMonth",OracleDbType.Date, msEntity.BeginAccountMonth,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), }; DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CWParas); if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False { return -998; } //符合以上条件方可通过验证 return 0; } catch (Exception ex) { throw ex; } } /// /// 编辑管理岗位人员明细 /// /// 策略ID /// 管理岗位人员数据源 /// 当前用户 /// 事务链接 /// 服务对象实体 public static ServiceResultEntity EditManagers(int ManagerSalaryID, DataSet dsDetail, SUserInfo userInfo, IDBTransaction oracleTrConn) { int RowsCount = 0; bool ConnIsNew = false; //如果链接为空，则新建链接 if (oracleTrConn == null) { oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ConnIsNew = true; } ServiceResultEntity srEntity = new ServiceResultEntity(); StringBuilder sbSql = new StringBuilder(); try { //如果是新链接，需要打开事务 if (ConnIsNew == true) { oracleTrConn.Connect(); } //首先验证管理者人员唯一性 String isHaveName = ValidationManagers(oracleTrConn, ManagerSalaryID, dsDetail); if(isHaveName != string.Empty) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Other; srEntity.Message = isHaveName + "已经是其他组内成员，不可设置为管理者."; return srEntity; } DataTable dtDetail = dsDetail.Tables[0]; foreach (DataRow drFor in dtDetail.Rows) { if (drFor.RowState == DataRowState.Added) //添加 { if (drFor["StaffID"] == DBNull.Value) { continue; } //获取序列ID sbSql.Clear(); sbSql.Append("select SEQ_TAT_Managers_ManagersID.nextval from dual"); int managerId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); //插入被复制的信息 sbSql.Clear(); sbSql.Append(@"Insert into TP_TAT_Managers (ManagersID,ManagerSalaryID,Manager,SalaryType,SalaryPercent, AccountID,CreateUserID,UpdateUserID) Values (:ManagersID,:ManagerSalaryID,:Manager,:SalaryType,:SalaryPercent, :AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] CMParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, managerId,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, ManagerSalaryID,ParameterDirection.Input), new OracleParameter(":Manager",OracleDbType.Int32, drFor["StaffID"],ParameterDirection.Input), new OracleParameter(":SalaryType",OracleDbType.Int32, drFor["SalaryType"],ParameterDirection.Input), new OracleParameter(":SalaryPercent",OracleDbType.Decimal, drFor["SalaryPercent"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CMParas); } else if (drFor.RowState == DataRowState.Modified) //修改 { sbSql.Clear(); sbSql.Append(@"Update TP_TAT_Managers Set SalaryType=:SalaryType, SalaryPercent=:SalaryPercent, UpdateUserID=:UpdateUserID Where ManagersID=:ManagersID"); OracleParameter[] UpdateParas = new OracleParameter[] { new OracleParameter(":SalaryType",OracleDbType.Int32, drFor["SalaryType"],ParameterDirection.Input), new OracleParameter(":SalaryPercent",OracleDbType.Int32, drFor["SalaryPercent"],ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":ManagersID",OracleDbType.Int32, drFor["ManagersID"],ParameterDirection.Input) }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas); } else if (drFor.RowState == DataRowState.Deleted) //删除 { sbSql.Clear(); //首先删除明细(组内成员) sbSql.Append(@"Delete from TP_TAT_Members Where ManagersID=:ManagersID"); OracleParameter[] DeleteParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, drFor["ManagersID",DataRowVersion.Original],ParameterDirection.Input) }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas); sbSql.Clear(); sbSql.Append(@"Delete from TP_TAT_Managers Where ManagersID=:ManagersID"); RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas); } } //不是新链接的情况下，直接Return出去 if (ConnIsNew == false) { return srEntity; } //新链接的情况下，如果插入失败则回滚事务并关闭 if (ConnIsNew == true && RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.ValidationFailed; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理岗位工资策略", "新建"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理岗位工资策略", "新建"); } return srEntity; } catch (Exception ex) { throw ex; } } /// /// 编辑管理组内成员明细 /// /// 管理岗位人员ID /// 数据源 /// 当前用户 /// 服务返回实体 public static ServiceResultEntity EditMembers(int ManagerSalaryID,int ManagersID, DataSet dsDetail, SUserInfo userInfo) { int RowCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); StringBuilder sbSql = new StringBuilder(); try { oracleTrConn.Connect(); //首先验证是否有重复人员 string isHaveName = ValidationMembers(oracleTrConn, ManagerSalaryID, dsDetail); if(isHaveName != string.Empty) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Other; srEntity.Message = isHaveName + "已经是管理者，不可加入其他组内."; return srEntity; } //如果可通过验证再进行操作 DataTable dtDetail = dsDetail.Tables[0]; foreach (DataRow drFor in dtDetail.Rows) { if (drFor.RowState == DataRowState.Added) //添加 { if (drFor["StaffID"] == DBNull.Value) { continue; } sbSql.Clear(); sbSql.Append(@"Insert into TP_TAT_Members (ManagersID,ManagerSalaryID,Member,AccountID,CreateUserID,UpdateUserID) Values (:ManagersID,:ManagerSalaryID,:Member,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] InsertParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, ManagersID,ParameterDirection.Input), new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, ManagerSalaryID,ParameterDirection.Input), new OracleParameter(":Member",OracleDbType.Int32, drFor["StaffID"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), InsertParas); } else if (drFor.RowState == DataRowState.Deleted) //删除 { sbSql.Clear(); sbSql.Append(@"Delete from TP_TAT_Members Where ManagersID=:ManagersID and Member=:Member"); OracleParameter[] DeleteParas = new OracleParameter[] { new OracleParameter(":ManagersID",OracleDbType.Int32, ManagersID,ParameterDirection.Input), new OracleParameter(":Member",OracleDbType.Int32, drFor["StaffId",DataRowVersion.Original],ParameterDirection.Input) }; RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas); } } if (RowCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.ValidationFailed; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "管理组内成员", "编辑"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "管理组内成员", "编辑"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 验证插入人员唯一性（管理者） /// /// 数据库连接 /// 管理岗位工资策略ID /// 即将插入的管理者 /// 重复的管理者姓名 private static string ValidationManagers(IDBTransaction trConn,int ManagerSalaryID,DataSet dsManagers) { try { string strManagersId = string.Empty; StringBuilder sbManagersId = new StringBuilder(); //获取即将插入的管理者员工ID字符串组 DataTable dtSourse = dsManagers.Tables[0]; foreach (DataRow drFor in dtSourse.Rows) { if (drFor.RowState == DataRowState.Added) { if (drFor["StaffID"] == DBNull.Value) { continue; } sbManagersId.Append(Convert.ToInt32(drFor["StaffID"]) + ","); } } if (sbManagersId.Length != 0) { //查询组员表中是否有这些即将插入的管理者 strManagersId = sbManagersId.ToString().Substring(0, sbManagersId.Length - 1); string strSql = @"Select StaffName from TP_TAT_Members Inner join TP_HR_STAFF on TP_TAT_Members.Member = TP_HR_STAFF.StaffID Where ManagerSalaryID=:ManagerSalaryID And Member in (" + strManagersId + ")"; OracleParameter[] ValidationParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, ManagerSalaryID,ParameterDirection.Input) }; DataTable dtIsHave = trConn.GetSqlResultToDt(strSql, ValidationParas); //遍历获取存在重复的员工姓名 StringBuilder sbStaffName = new StringBuilder(); foreach (DataRow drFor in dtIsHave.Rows) { sbStaffName.Append(drFor["StaffName"].ToString() + ","); } //如果存在，返回姓名字符串数组，不存在，返回空 if(sbStaffName.Length >0) { return sbStaffName.ToString().Substring(0, sbStaffName.Length - 1); } else { return string.Empty; } } return string.Empty; } catch (Exception ex) { throw ex; } } /// /// 验证插入人员唯一性（组内人员） /// /// 数据库连接 /// 管理岗位工资策略ID /// 即将插入的组内人员 /// 重复的组内成员姓名 private static string ValidationMembers(IDBTransaction trConn, int ManagerSalaryID, DataSet dsMember) { try { string strMembersId = string.Empty; StringBuilder sbMemberId = new StringBuilder(); //获取即将插入的组内成员员工ID字符串组 DataTable dtSourse = dsMember.Tables[0]; foreach (DataRow drFor in dtSourse.Rows) { if (drFor.RowState == DataRowState.Added) { if (drFor["StaffID"] == DBNull.Value) { continue; } sbMemberId.Append(Convert.ToInt32(drFor["StaffID"]) + ","); } } if (sbMemberId.Length != 0) { //查询管理者表中是否有这些即将插入的组内成员 strMembersId = sbMemberId.ToString().Substring(0, sbMemberId.Length - 1); string strSql = @"Select StaffName from TP_TAT_Managers Inner join TP_HR_STAFF on TP_TAT_Managers.Manager = TP_HR_STAFF.StaffID Where ManagerSalaryID=:ManagerSalaryID And Manager in (" + strMembersId + ")"; OracleParameter[] ValidationParas = new OracleParameter[] { new OracleParameter(":ManagerSalaryID",OracleDbType.Int32, ManagerSalaryID,ParameterDirection.Input) }; DataTable dtIsHave = trConn.GetSqlResultToDt(strSql, ValidationParas); //遍历获取存在重复的员工姓名 StringBuilder sbStaffName = new StringBuilder(); foreach (DataRow drFor in dtIsHave.Rows) { sbStaffName.Append(drFor["StaffName"].ToString() + ","); } //如果存在，返回姓名字符串数组，不存在，返回空 if (sbStaffName.Length > 0) { return sbStaffName.ToString().Substring(0, sbStaffName.Length - 1); } else { return string.Empty; } } return string.Empty; } catch (Exception ex) { throw ex; } } #endregion #region 出勤考核明细 /// /// 新建出勤考核明细 /// /// 新建实体 /// 对应出勤考核扣罚明细 /// 当前用户信息 /// 服务返回实体 public static ServiceResultEntity AddAttendanceDetail(AttendanceDetailEntity attendanceDetailEntity, DataSet dsDetail, SUserInfo userInfo) { int RowsCount = 0; //int CopyCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); StringBuilder sbSql = new StringBuilder(); //获取序列ID sbSql.Clear(); sbSql.Append("select SEQ_TAT_ATTENDANCE_ID.nextval from dual"); int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString())); sbSql.Clear(); //添加出勤考核明细主体信息 sbSql.Append(@"Insert into TP_TAT_AttendanceDetail (AttendanceID,JobsID,RestmMode,Monday,Tuesday, Wednesday,Thursday,Friday,Saturday,Sunday,RestDays,PunishMode,AccountID,CreateUserID,UpdateUserID) Values (:AttendanceID,:JobsID,:RestmMode,:Monday,:Tuesday, :Wednesday,:Thursday,:Friday,:Saturday,:Sunday,:RestDays,:PunishMode,:AccountID,:CreateUserID,:UpdateUserID)"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":AttendanceID",OracleDbType.Int32, entityId,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, attendanceDetailEntity.JobsID,ParameterDirection.Input), new OracleParameter(":RestmMode",OracleDbType.Int32, attendanceDetailEntity.RestmMode,ParameterDirection.Input), new OracleParameter(":Monday",OracleDbType.Int32, attendanceDetailEntity.Monday,ParameterDirection.Input), new OracleParameter(":Tuesday",OracleDbType.Int32, attendanceDetailEntity.Tuesday,ParameterDirection.Input), new OracleParameter(":Wednesday",OracleDbType.Int32, attendanceDetailEntity.Wednesday,ParameterDirection.Input), new OracleParameter(":Thursday",OracleDbType.Int32, attendanceDetailEntity.Thursday,ParameterDirection.Input), new OracleParameter(":Friday",OracleDbType.Int32, attendanceDetailEntity.Friday,ParameterDirection.Input), new OracleParameter(":Saturday",OracleDbType.Int32, attendanceDetailEntity.Saturday,ParameterDirection.Input), new OracleParameter(":Sunday",OracleDbType.Int32, attendanceDetailEntity.Sunday,ParameterDirection.Input), new OracleParameter(":RestDays",OracleDbType.Decimal, attendanceDetailEntity.RestDays,ParameterDirection.Input), new OracleParameter(":PunishMode",OracleDbType.Int32, attendanceDetailEntity.PunishMode,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), }; //连接数据库并返回结果 RowsCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); //如果插入失败则回滚事务并关闭 if (RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.ValidationFailed; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "出勤考核明细", "新建"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "出勤考核明细", "新建"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 编辑出勤考核明细 /// /// 工价实体 /// 工价对应工种关系数据源 /// 当前用户 /// 服务对象实体 public static ServiceResultEntity EditAttendanceDetail(AttendanceDetailEntity attendanceDetailEntity, DataSet dsDetail, SUserInfo userInfo) { int RowsCount = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity srEntity = new ServiceResultEntity(); try { oracleTrConn.Connect(); StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Update TP_TAT_AttendanceDetail Set RestmMode=:RestmMode, Monday=:Monday, Tuesday=:Tuesday, Wednesday=:Wednesday, Thursday=:Thursday, Friday=:Friday, Saturday=:Saturday, Sunday=:Sunday, RestDays=:RestDays, PunishMode=:PunishMode, UpdateUserID=:UpdateUserID Where AttendanceID=:AttendanceID and JobsID=:JobsID and OPTimeStamp=:OPTimeStamp"); OracleParameter[] WParas = new OracleParameter[] { new OracleParameter(":RestmMode",OracleDbType.Int32, attendanceDetailEntity.RestmMode,ParameterDirection.Input), new OracleParameter(":Monday",OracleDbType.Int32, attendanceDetailEntity.Monday,ParameterDirection.Input), new OracleParameter(":Tuesday",OracleDbType.Int32, attendanceDetailEntity.Tuesday,ParameterDirection.Input), new OracleParameter(":Wednesday",OracleDbType.Int32, attendanceDetailEntity.Wednesday,ParameterDirection.Input), new OracleParameter(":Thursday",OracleDbType.Int32, attendanceDetailEntity.Thursday,ParameterDirection.Input), new OracleParameter(":Friday",OracleDbType.Int32, attendanceDetailEntity.Friday,ParameterDirection.Input), new OracleParameter(":Saturday",OracleDbType.Int32, attendanceDetailEntity.Saturday,ParameterDirection.Input), new OracleParameter(":Sunday",OracleDbType.Int32, attendanceDetailEntity.Sunday,ParameterDirection.Input), new OracleParameter(":RestDays",OracleDbType.Decimal, attendanceDetailEntity.RestDays,ParameterDirection.Input), new OracleParameter(":PunishMode",OracleDbType.Int32, attendanceDetailEntity.PunishMode,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AttendanceID",OracleDbType.Int32, attendanceDetailEntity.AttendanceID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, attendanceDetailEntity.JobsID,ParameterDirection.Input), new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp, attendanceDetailEntity.OPTimeStamp,ParameterDirection.Input), }; //连接数据库并返回结果 RowsCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas); //并更新对应扣罚明细 if (attendanceDetailEntity.CopyId == 0) { EditAttFinesRules(Convert.ToInt32(attendanceDetailEntity.AttendanceID), Convert.ToInt32(attendanceDetailEntity.JobsID), dsDetail, userInfo, oracleTrConn); } //如果插入失败则回滚事务并关闭 if (RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.DataChanged; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "出勤考核明细", "修改"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "出勤考核明细", "修改"); } return srEntity; } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } srEntity.Exception = ex; srEntity.Status = Constant.ServiceResultStatus.SystemError; return srEntity; } } /// /// 编辑出勤考核明细对应扣罚 /// /// 出勤考核ID /// 对应工种数据源 /// 当前用户 /// 事务链接 /// 服务对象实体 public static ServiceResultEntity EditAttFinesRules(int AttendanceID,int JobsID, DataSet dsDetail, SUserInfo userInfo, IDBTransaction oracleTrConn) { int RowsCount = 0; bool ConnIsNew = false; //如果链接为空，则新建链接 if (oracleTrConn == null) { oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); ConnIsNew = true; } ServiceResultEntity srEntity = new ServiceResultEntity(); StringBuilder sbSql = new StringBuilder(); try { //如果是新链接，需要打开事务 if (ConnIsNew == true) { oracleTrConn.Connect(); } DataTable dtDetail = dsDetail.Tables[0]; foreach (DataRow drFor in dtDetail.Rows) { //dsDetail.Tables[0].RejectChanges(); if (drFor.RowState == DataRowState.Added) //添加 { sbSql.Clear(); sbSql.Append(@"Insert into TP_TAT_AttFinesRules(AttendanceID,JobsID,BeginAbsentDays, EndAbsentDays,PunishAmount,AccountID, CreateUserID,UpdateUserID) Values(:AttendanceID,:JobsID,:BeginAbsentDays,:EndAbsentDays, :PunishAmount,:AccountID, :CreateUserID,:UpdateUserID)"); OracleParameter[] InsertParas = new OracleParameter[] { new OracleParameter(":AttendanceID",OracleDbType.Int32, AttendanceID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, JobsID,ParameterDirection.Input), new OracleParameter(":BeginAbsentDays",OracleDbType.Decimal, drFor["BeginAbsentDays"],ParameterDirection.Input), new OracleParameter(":EndAbsentDays",OracleDbType.Decimal, drFor["EndAbsentDays"],ParameterDirection.Input), new OracleParameter(":PunishAmount",OracleDbType.Decimal, drFor["PunishAmount"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input) }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), InsertParas); } else if (drFor.RowState == DataRowState.Modified) //修改 { sbSql.Clear(); sbSql.Append(@"Update TP_TAT_AttFinesRules Set BeginAbsentDays=:BeginAbsentDays, EndAbsentDays=:EndAbsentDays, PunishAmount=:PunishAmount, UpdateUserID=:UpdateUserID Where AttendanceID=:AttendanceID and JobsID=:JobsID and FinesRulesID=:FinesRulesID"); OracleParameter[] UpdateParas = new OracleParameter[] { new OracleParameter(":BeginAbsentDays",OracleDbType.Decimal, drFor["BeginAbsentDays"],ParameterDirection.Input), new OracleParameter(":EndAbsentDays",OracleDbType.Decimal, drFor["EndAbsentDays"],ParameterDirection.Input), new OracleParameter(":PunishAmount",OracleDbType.Decimal, drFor["PunishAmount"],ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, userInfo.UserID,ParameterDirection.Input), new OracleParameter(":AttendanceID",OracleDbType.Int32, AttendanceID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, JobsID,ParameterDirection.Input), new OracleParameter(":FinesRulesID",OracleDbType.Int32, drFor["FinesRulesID"],ParameterDirection.Input), }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas); } else if (drFor.RowState == DataRowState.Deleted) //删除 { drFor.RejectChanges(); sbSql.Clear(); sbSql.Append(@"Delete from TP_TAT_AttFinesRules Where AttendanceID=:AttendanceID and JobsID=:JobsID and FinesRulesID=:FinesRulesID"); OracleParameter[] DeleteParas = new OracleParameter[] { new OracleParameter(":AttendanceID",OracleDbType.Int32, AttendanceID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, JobsID,ParameterDirection.Input), new OracleParameter(":FinesRulesID",OracleDbType.Int32, drFor["FinesRulesID"],ParameterDirection.Input), }; RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas); sbSql.Clear(); } } //不是新链接的情况下，直接Return出去 if (ConnIsNew == false) { return srEntity; } //新链接的情况下，如果插入失败则回滚事务并关闭 if (ConnIsNew == true && RowsCount == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.ValidationFailed; srEntity.Message = string.Format(Messages.MSG_CMN_W001, "出勤考核明细", "新建"); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); srEntity.Status = Constant.ServiceResultStatus.Success; srEntity.Message = string.Format(Messages.MSG_CMN_I001, "出勤考核明细", "新建"); } return srEntity; } catch (Exception ex) { throw ex; } } #endregion } }