/*******************************************************************************
* Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
* 类的信息：
* 1.程序名称：TATModuleLogicDAL.cs
* 2.功能描述：策略管理数据库访问类（插入、修改、删除）
* 编辑履历：
* 作者 日期 版本 修改内容
* 庄天威 2014/11/18 1.00 新建
*******************************************************************************/
using System;
using System.Data;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.DataAccess.Client;
namespace Dongke.IBOSS.PRD.Service.TATModuleLogic
{
///
/// 策略管理数据库访问类（插入、修改、删除）
///
public partial class TATModuleLogicDAL
{
#region 缺陷扣罚
///
/// 新建缺陷扣罚策略
///
/// 新建实体
/// 用户基本信息
///
/// ServiceResultEntity受影响行数
///
public static ServiceResultEntity AddDefectFine(DefectFineEntity dfEntity, 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_DefectFine_ID.nextval from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
sbSql.Clear();
//添加缺陷扣罚策略主体信息
sbSql.Append(@"Insert into TP_TAT_DefectFine
(DefectFineID,DefectFineName,SalaryType,DefectFine,FineType,Coefficient,
BeginAccountMonth,Remarks,AuditStatus,AccountID,CreateUserID,UpdateUserID)
Values
(:DefectFineID,:DefectFineName,:SalaryType,:DefectFine,:FineType,:Coefficient,
:BeginAccountMonth,:Remarks,:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":DefectFineName",OracleDbType.NVarchar2,
dfEntity.DefectFineName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":DefectFine",OracleDbType.Int32,
dfEntity.DefectFine,ParameterDirection.Input),
new OracleParameter(":FineType",OracleDbType.Int32,
dfEntity.FineType,ParameterDirection.Input),
new OracleParameter(":Coefficient",OracleDbType.Decimal,
dfEntity.Coefficient,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//如果是复制添加,那么还要将复制主体信息的明细全部添加
if (dfEntity.CopyId != 0)
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_DefectFineDetail
(DefectFineID,JobsID,GoodsID,FineAmount,AccountID,CreateUserID,UpdateUserID)
Select :DefectFineID,JobsID,GoodsID,FineAmount,AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_DefectFineDetail Where DefectFineID = :CopyDefectFineID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",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(":CopyDefectFineID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 修改缺陷扣罚信息
///
/// 修改实体
/// 用户基本信息
/// ServiceResultEntity受影响行数
public static ServiceResultEntity UpdateDefectFine(DefectFineEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 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_DefectFine
Set DefectFineName=:DefectFineName,
SalaryType=:SalaryType,
DefectFine=:DefectFine,
FineType=:FineType,
Coefficient=:Coefficient,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where DefectFineID=:DefectFineID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":DefectFineName",OracleDbType.NVarchar2,
dfEntity.DefectFineName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":DefectFine",OracleDbType.Int32,
dfEntity.DefectFine,ParameterDirection.Input),
new OracleParameter(":FineType",OracleDbType.Int32,
dfEntity.FineType,ParameterDirection.Input),
new OracleParameter(":Coefficient",OracleDbType.Decimal,
dfEntity.Coefficient,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":DefectFineID",OracleDbType.Int32,
dfEntity.DefectFineID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
/// int
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationDefectFine(IDBTransaction trConn,
DefectFineEntity dfEntity, 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,
dfEntity.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_DefectFine
Where SalaryType = :SalaryType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and DefectFine=:DefectFine
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
new OracleParameter(":DefectFine",OracleDbType.Int32,
dfEntity.DefectFine,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批缺陷扣罚策略
///
/// 审批策略实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditDefectFine(DefectFineEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationDefectFine(oracleTrConn, dfEntity, 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_DefectFine
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where DefectFineID=:DefectFineID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":DefectFineID",OracleDbType.Int32,
dfEntity.DefectFineID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 停用缺陷扣罚策略
///
/// 停用实体
/// 用户基本信息
/// ServiceResultEntity返回影响行
public static ServiceResultEntity StopDefectFine(DefectFineEntity dfEntity, 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_DefectFine
Set ValueFlag = 0
Where DefectFineID=:DefectFineID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
dfEntity.DefectFineID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_DefectFineDetail
Where DefectFineId = :DefectFineID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
dfEntity.DefectFineID,ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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
/// 明细数据源
/// 用户基本信息
/// ServiceResultEntity
public static ServiceResultEntity EditDefectFineDetail(int DefectFineID, DataSet dsSourse, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
StringBuilder sbSql = new StringBuilder();
//遍历工种，如果有被删除的工种的话，那么将该工种下的全部明细全部删除
DataTable dtJobsSourse = dsSourse.Tables[0];
foreach (DataRow drFor in dtJobsSourse.Rows)
{
if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_DefectFineDetail
Where DefectFineID = :DefectFineID
and JobsID = :JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
DefectFineID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
}
}
//获取数据源并遍历明细
DataTable dtSourse = dsSourse.Tables[1];
foreach (DataRow drFor in dtSourse.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["JobsId"] == DBNull.Value || drFor["GoodsID"] == DBNull.Value)
{
continue;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_DefectFineDetail(DefectFineID,JobsID,GoodsID,
FineAmount,AccountID,CreateUserID,UpdateUserID)
Values(:DefectFineID,:JobsID,:GoodsID,
:FineAmount,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
DefectFineID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
new OracleParameter(":FineAmount",OracleDbType.Decimal,
drFor["FineAmount"],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.Modified) //修改
{
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_DefectFineDetail
Set FineAmount = :FineAmount,
UpdateUserID = :UpdateUserID
Where DefectFineID = :DefectFineID and JobsID = :JobsID and GoodsID = :GoodsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":FineAmount",OracleDbType.Decimal,
drFor["FineAmount"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":DefectFineID",OracleDbType.Int32,
DefectFineID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_DefectFineDetail
Where DefectFineID = :DefectFineID
and JobsID = :JobsID
and GoodsID = :GoodsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":DefectFineID",OracleDbType.Int32,
DefectFineID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsId",DataRowVersion.Original],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
}
}
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;
}
}
#endregion
#region 工价策略
///
/// 新建工价策略
///
/// 新建实体
/// 对应工种明细
/// 用户基本信息
/// ServiceResultEntity服务返回实体
public static ServiceResultEntity AddWages(WagesEntity wagesEntity, 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_Wages_WagesID.nextval from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
sbSql.Clear();
//添加工价策略主体信息
sbSql.Append(@"Insert into TP_TAT_Wages
(WagesID,WagesName,SalaryType,BeginAccountMonth,Remarks,
AuditStatus,AccountID,CreateUserID,UpdateUserID)
Values
(:WagesID,:WagesName,:SalaryType,:BeginAccountMonth,:Remarks,
:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] WParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":WagesName",OracleDbType.NVarchar2,
wagesEntity.WagesName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
wagesEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
wagesEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
wagesEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
wagesEntity.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 (wagesEntity.CopyId == 0)
{
EditWagesDetail(entityId, dsDetail, userInfo, oracleTrConn);
}
//如果是复制添加,那么还要将复制主体信息的明细全部添加
else
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_WagesDetail
(WagesID,JobsID,PriceType,Weight,WeightPricing,KilnCarNum,CarPricing,
AccountID,CreateUserID,UpdateUserID)
Select :WagesID,JobsID,PriceType,Weight,WeightPricing,KilnCarNum,CarPricing,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_WagesDetail Where WagesID = :CopyWagesID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":WagesID",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(":CopyWagesID",OracleDbType.Int32,
wagesEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_WagesGoodsDetail
(WagesID,JobsID,GoodsID,Wages,PublicWages,AccountID
,CreateUserID,UpdateUserID)
Select :WagesID,JobsID,GoodsID,Wages,PublicWages,AccountID
,:CreateUserID,:UpdateUserID
From TP_TAT_WagesGoodsDetail where WagesID = :CopyWagesID");
CopyCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 编辑工价策略
///
/// 工价实体
/// 工价对应工种关系数据源
/// 用户基本信息
/// ServiceResultEntity服务对象实体
public static ServiceResultEntity EditWages(WagesEntity wagesEntity, 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_Wages
Set WagesName=:WagesName,
SalaryType=:SalaryType,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where WagesID=:WagesID and OPTimeStamp=:OPTimeStamp");
OracleParameter[] WParas = new OracleParameter[] {
new OracleParameter(":WagesName",OracleDbType.NVarchar2,
wagesEntity.WagesName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
wagesEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
wagesEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
wagesEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":WagesID",OracleDbType.Int32,
wagesEntity.WagesID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
wagesEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowsCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WParas);
//并更新对应工种明细
EditWagesDetail(Convert.ToInt32(wagesEntity.WagesID), 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 AuditWages(WagesEntity wagesEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (wagesEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationWages(oracleTrConn, wagesEntity, 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_Wages
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where WagesID=:WagesID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] WParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
wagesEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":WagesID",OracleDbType.Int32,
wagesEntity.WagesID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
wagesEntity.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;
}
}
///
/// 停用工价策略
///
/// 停用实体
/// 用户基本信息
/// ServiceResultEntity返回影响行
public static ServiceResultEntity StopWages(WagesEntity wagesEntity, 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_Wages
Set ValueFlag = 0
Where WagesID=:WagesID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] WParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
wagesEntity.WagesID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
wagesEntity.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_WagesDetail
Where WagesID = :WagesID");
OracleParameter[] WDParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
wagesEntity.WagesID,ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), WDParas);
//停用工价策略产品明细
sbSql.Clear();
sbSql.Append(@"Delete from TP_TAT_WagesGoodsDetail
Where WagesID = :WagesID");
OracleParameter[] WGDParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
wagesEntity.WagesID,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 ValidationWages(IDBTransaction trConn,
WagesEntity wagesEntity, 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,
wagesEntity.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_Wages
Where SalaryType = :SalaryType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CWParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
wagesEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
wagesEntity.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
/// 对应工种数据源
/// 用户基本信息
/// 事务链接
/// ServiceResultEntity服务对象实体
public static ServiceResultEntity EditWagesDetail(int WagesID, 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)
{
//根据当前行的计件类别判断以下数值
decimal? weight = null;
decimal? weightPricing = null;
decimal? kilnCarNum = null;
decimal? CarPricing = null;
if (drFor.RowState != DataRowState.Deleted && drFor.RowState != DataRowState.Detached)
{
if (drFor["PriceType"] == DBNull.Value)
{
continue;
}
if (Convert.ToInt32(drFor["PriceType"]) == 3)
{
kilnCarNum = Convert.ToDecimal(drFor["OneCount"]);
CarPricing = Convert.ToDecimal(drFor["OnePrice"]);
}
else if (Convert.ToInt32(drFor["PriceType"]) == 4)
{
weight = Convert.ToDecimal(drFor["OneCount"]);
weightPricing = Convert.ToDecimal(drFor["OnePrice"]);
}
}
if (drFor.RowState == DataRowState.Added) //添加
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_WagesDetail(WagesID,JobsID,PriceType,
Weight,WeightPricing,KilnCarNum,CarPricing,AccountID,
CreateUserID,UpdateUserID)
Values(:WagesID,:JobsID,:PriceType,
:Weight,:WeightPricing,:KilnCarNum,:CarPricing,:AccountID,
:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":PriceType",OracleDbType.Int32,
drFor["PriceType"],ParameterDirection.Input),
new OracleParameter(":Weight",OracleDbType.Decimal,
weight,ParameterDirection.Input),
new OracleParameter(":WeightPricing",OracleDbType.Decimal,
weightPricing,ParameterDirection.Input),
new OracleParameter(":KilnCarNum",OracleDbType.Decimal,
kilnCarNum,ParameterDirection.Input),
new OracleParameter(":CarPricing",OracleDbType.Decimal,
CarPricing,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_WagesDetail
Set PriceType=:PriceType,
Weight=:Weight,
WeightPricing=:WeightPricing,
KilnCarNum=:KilnCarNum,
CarPricing=:CarPricing,
UpdateUserID=:UpdateUserID
Where WagesID=:WagesID and JobsID=:JobsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":PriceType",OracleDbType.Int32,
drFor["PriceType"],ParameterDirection.Input),
new OracleParameter(":Weight",OracleDbType.Decimal,
weight,ParameterDirection.Input),
new OracleParameter(":WeightPricing",OracleDbType.Decimal,
weightPricing,ParameterDirection.Input),
new OracleParameter(":KilnCarNum",OracleDbType.Decimal,
kilnCarNum,ParameterDirection.Input),
new OracleParameter(":CarPricing",OracleDbType.Decimal,
CarPricing,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
};
RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete from TP_TAT_WagesDetail
Where WagesID=:WagesID and JobsID=:JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
sbSql.Clear();
//并且要删除该工种下所有产品明细
sbSql.Append(@"Delete from TP_TAT_WagesGoodsDetail
Where WagesID=:WagesID and JobsID=:JobsID");
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
/// 对应工种ID
/// 数据源
/// 用户基本信息
/// ServiceResultEntity服务返回实体
public static ServiceResultEntity EditWagesGoodsDetail(int WagesID, int JobsID, 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();
DataTable dtDetail = dsDetail.Tables[0];
foreach (DataRow drFor in dtDetail.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["GoodsID"] == DBNull.Value)
{
continue;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_WagesGoodsDetail
(WagesID,JobsID,GoodsID,PieceNum,Wages,PublicWages,AccountID,
CreateUserID,UpdateUserID)
Values
(:WagesID,:JobsID,:GoodsID,:PieceNum,:Wages,:PublicWages,:AccountID,
:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
JobsID,ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
new OracleParameter(":PieceNum",OracleDbType.Decimal,
drFor["PieceNum"],ParameterDirection.Input),
new OracleParameter(":Wages",OracleDbType.Decimal,
drFor["Wages"],ParameterDirection.Input),
new OracleParameter(":PublicWages",OracleDbType.Decimal,
drFor["PublicWages"],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.Modified) //修改
{
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_WagesGoodsDetail
Set PieceNum=:PieceNum,
Wages=:Wages,
PublicWages=:PublicWages,
UpdateUserID=:UpdateUserID
Where WagesID=:WagesID and JobsID=:JobsID and GoodsID=:GoodsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":PieceNum",OracleDbType.Decimal,
drFor["PieceNum"],ParameterDirection.Input),
new OracleParameter(":Wages",OracleDbType.Decimal,
drFor["Wages"],ParameterDirection.Input),
new OracleParameter(":PublicWages",OracleDbType.Decimal,
drFor["PublicWages"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete from TP_TAT_WagesGoodsDetail
Where WagesID=:WagesID and JobsID=:JobsID and GoodsID=:GoodsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":WagesID",OracleDbType.Int32,
WagesID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID",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;
}
}
#endregion
#region 基本工资策略
///
/// 新建基本工资策略
///
/// 新建实体
/// 用户基本信息
/// ServiceResultEntity受影响行数
///
/// 2014.12.11 任海 新建
///
public static ServiceResultEntity AddBasicSalary(BasicSalaryEntity dfEntity, 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_BasicSalary_ID.nextval from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
sbSql.Clear();
//添加基本工资策略主体信息
sbSql.Append(@"Insert into TP_TAT_BasicSalary
(BasicSalaryID,BasicSalaryName,SalaryType,
BeginAccountMonth,Remarks,AuditStatus,AccountID,CreateUserID,UpdateUserID)
Values
(:BasicSalaryID,:BasicSalaryName,:SalaryType,
:BeginAccountMonth,:Remarks,:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":BasicSalaryName",OracleDbType.NVarchar2,
dfEntity.BasicSalaryName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//如果是复制添加,那么还要将复制主体信息的明细全部添加
if (dfEntity.CopyId != 0)
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_BasicSalaryDetail
(BasicSalaryID,JobsID,BasicSalary,Subsidy,MinimumSalary,AccountID,CreateUserID,UpdateUserID)
Select :BasicSalaryID,JobsID,BasicSalary,Subsidy,MinimumSalary,AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_BasicSalaryDetail Where BasicSalaryID = :CopyDefectFineID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",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(":CopyDefectFineID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 修改基本工资信息
///
/// 修改实体
/// 用户基本信息
/// 受影响行数
///
/// 2014.12.11 任海 新建
///
public static ServiceResultEntity UpdateBasicSalary(BasicSalaryEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 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_BasicSalary
Set BasicSalaryName=:BasicSalaryName,
SalaryType=:SalaryType,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where BasicSalaryID=:BasicSalaryID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryName",OracleDbType.NVarchar2,
dfEntity.BasicSalaryName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
dfEntity.BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 停用基本工资策略
///
/// 停用实体
/// 用户基本信息
/// ServiceResultEntity返回影响行
public static ServiceResultEntity StopBasicSalary(BasicSalaryEntity dfEntity, 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_BasicSalary
Set ValueFlag = 0
Where BasicSalaryID=:BasicSalaryID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
dfEntity.BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_BasicSalaryDetail
Where BasicSalaryID = :BasicSalaryID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
dfEntity.BasicSalaryID,ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
/// int
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationBasicSalary(IDBTransaction trConn,
BasicSalaryEntity dfEntity, 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,
dfEntity.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_BasicSalary
Where SalaryType = :SalaryType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批基本工资策略
///
/// 审批策略实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditBasicSalary(BasicSalaryEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationBasicSalary(oracleTrConn, dfEntity, 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_BasicSalary
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where BasicSalaryID=:BasicSalaryID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
dfEntity.BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 编辑基本工资对应工种明细
///
/// 工价策略ID
/// 对应工种数据源
/// 用户基本信息
/// 数据连接事务
/// ServiceResultEntity服务对象实体
///
/// 2014.12.11 任海 新建
///
public static ServiceResultEntity EditBasicSalaryDetail(int BasicSalaryID, 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)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["JobsId"] == DBNull.Value)
{
continue;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_BasicSalaryDetail(BasicSalaryID,JobsID,BasicSalary,
Subsidy,MinimumSalary,AccountID,
CreateUserID,UpdateUserID)
Values(:BasicSalaryID,:JobsID,:BasicSalary,
:Subsidy,:MinimumSalary,:AccountID,
:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":BasicSalary",OracleDbType.Decimal,
drFor["BasicSalary"],ParameterDirection.Input),
new OracleParameter(":Subsidy",OracleDbType.Decimal,
drFor["Subsidy"],ParameterDirection.Input),
new OracleParameter(":MinimumSalary",OracleDbType.Decimal,
drFor["MinimumSalary"],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_BasicSalaryDetail
Set BasicSalary=:BasicSalary,
Subsidy=:Subsidy,
MinimumSalary=:MinimumSalary,
UpdateUserID=:UpdateUserID
Where BasicSalaryID=:BasicSalaryID and JobsID=:JobsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":BasicSalary",OracleDbType.Int32,
drFor["BasicSalary"],ParameterDirection.Input),
new OracleParameter(":Subsidy",OracleDbType.Decimal,
drFor["Subsidy"],ParameterDirection.Input),
new OracleParameter(":MinimumSalary",OracleDbType.Decimal,
drFor["MinimumSalary"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
};
RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete from TP_TAT_BasicSalaryDetail
Where BasicSalaryID=:BasicSalaryID and JobsID=:JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":BasicSalaryID",OracleDbType.Int32,
BasicSalaryID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],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
#region 出勤考核
///
/// 新建出勤考核
///
/// 新建实体
/// 用户基本信息
/// ServiceResultEntity受影响行数
///
/// 2014.12.15 任海 新建
///
public static ServiceResultEntity AddAttendance(AttendanceEntity dfEntity, 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_Attendance
(AttendanceID,AttendanceName,
BeginAccountMonth,Remarks,AuditStatus,AccountID,CreateUserID,UpdateUserID)
Values
(:AttendanceID,:AttendanceName,
:BeginAccountMonth,:Remarks,:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":AttendanceName",OracleDbType.NVarchar2,
dfEntity.AttendanceName,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//正常添加的情况，要同时插入工种关联信息
if (dfEntity.CopyId == 0)
{
EditAttendanceDetail(entityId, dsDetail, userInfo, oracleTrConn);
}
//如果是复制添加,那么还要将复制主体信息的明细全部添加
else
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_AttendanceDetail
(AttendanceID,JobsID,RestmMode,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,RestDays,PunishMode,
AccountID,CreateUserID,UpdateUserID)
Select :AttendanceID,JobsID,RestmMode,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,RestDays,PunishMode,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_AttendanceDetail Where AttendanceID = :CopyWagesID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",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(":CopyWagesID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_AttFinesRules
(AttendanceID,JobsID,BeginAbsentDays,EndAbsentDays,PunishAmount,
AccountID,CreateUserID,UpdateUserID)
Select :AttendanceID,JobsID,BeginAbsentDays,EndAbsentDays,PunishAmount,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_AttFinesRules Where AttendanceID = :CopyWagesID");
OracleParameter[] CopyAttFinesRules = new OracleParameter[] {
new OracleParameter(":AttendanceID",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(":CopyWagesID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyAttFinesRules);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 修改出勤考核信息
///
/// 修改实体
/// 对应工种数据源
/// 用户基本信息
/// ServiceResultEntity受影响行数
///
/// 2014.12.15 任海 新建
///
public static ServiceResultEntity UpdateAttendance(AttendanceEntity dfEntity, DataSet dsDetail, SUserInfo userInfo)
{
int RowCount = 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_Attendance
Set AttendanceName=:AttendanceName,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where AttendanceID=:AttendanceID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AttendanceName",OracleDbType.NVarchar2,
dfEntity.AttendanceName,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AttendanceID",OracleDbType.Int32,
dfEntity.AttendanceID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//并更新对应工种明细
EditAttendanceDetail(Convert.ToInt32(dfEntity.AttendanceID), dsDetail, userInfo, oracleTrConn);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 编辑出勤考核对应工种明细
///
/// 工价策略ID
/// 对应工种数据源
/// 用户基本信息
/// 事务链接
/// ServiceResultEntity服务对象实体
public static ServiceResultEntity EditAttendanceDetail(int AttendanceID, 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)
{
if (drFor.RowState == DataRowState.Added) //添加
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_AttendanceDetail(AttendanceID,JobsID,AccountID,
CreateUserID,UpdateUserID)
Values(:AttendanceID,:JobsID,:AccountID,
:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",OracleDbType.Int32,
AttendanceID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],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_AttendanceDetail
Set UpdateUserID=:UpdateUserID
Where AttendanceID=:AttendanceID and JobsID=:JobsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":WagesID",OracleDbType.Int32,
AttendanceID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
};
RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete from TP_TAT_AttendanceDetail
Where AttendanceID=:AttendanceID and JobsID=:JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",OracleDbType.Int32,
AttendanceID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
RowsCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
sbSql.Clear();
//并且要删除该工种下所有产品明细
sbSql.Append(@"Delete from TP_TAT_AttFinesRules
Where AttendanceID=:AttendanceID and JobsID=:JobsID and FinesRulesID= :FinesRulesID");
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;
}
}
///
/// 停用出勤考核
///
/// 停用实体
/// 用户基本信息
/// ServiceResultEntity返回影响行
public static ServiceResultEntity StopAttendance(AttendanceEntity dfEntity, 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_Attendance
Set ValueFlag = 0
Where AttendanceID=:AttendanceID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",OracleDbType.Int32,
dfEntity.AttendanceID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_AttendanceDetail
Where AttendanceID = :AttendanceID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":AttendanceID",OracleDbType.Int32,
dfEntity.AttendanceID,ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
/// int
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationAttendance(IDBTransaction trConn,
AttendanceEntity dfEntity, 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,
dfEntity.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_Attendance
Where BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonth,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批出勤考核
///
/// 审批出勤考核实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditAttendance(AttendanceEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationAttendance(oracleTrConn, dfEntity, 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_Attendance
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where AttendanceID=:AttendanceID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":AttendanceID",OracleDbType.Int32,
dfEntity.AttendanceID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
#endregion
#region 质量考核
///
/// 新建质量考核策略
///
/// 新建实体
/// 用户基本信息
///
/// ServiceResultEntity受影响行数
///
public static ServiceResultEntity AddQuality(QualityEntity dfEntity, 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_QUALITY_QUALITYID.NEXTVAL from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
//添加缺陷扣罚策略主体信息
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_Quality
(QualityID,QualityName,SalaryType,
BeginAccountMonth,Remarks,
AuditStatus,AccountID,
CreateUserID,UpdateUserID)
Values
(:QualityID,:QualityName,:SalaryType,
:BeginAccountMonth,:Remarks,
:AuditStatus,:AccountID,
:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":QualityName",OracleDbType.NVarchar2,
dfEntity.QualityName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//如果是复制添加,那么还要将复制主体信息的明细全部添加
if (dfEntity.CopyId != 0)
{
//添加质量明细
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_QualityDetail
(QualityID,JobsID,BaseAmount,IsEnd,AccountID,CreateUserID,UpdateUserID)
Select :QualityID,JobsID,BaseAmount,IsEnd,AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_QualityDetail Where QualityID = :CopyQualityID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":QualityID",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(":CopyQualityID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
//添加质量产品明细
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_QualityGoodsDetail
(QualityID,JobsID,GoodsID,ComplianceRate,IncrementSeed,IncrementAmount,
AccountID,CreateUserID,UpdateUserID)
Select :QualityID,JobsID,GoodsID,ComplianceRate,IncrementSeed,IncrementAmount,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_QualityGoodsDetail Where QualityID = :CopyQualityID");
OracleParameter[] CopyGoodsParas = new OracleParameter[] {
new OracleParameter(":QualityID",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(":CopyQualityID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyGoodsParas);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 修改质量考核信息
///
/// 修改实体
/// 用户基本信息
/// ServiceResultEntity受影响行数
public static ServiceResultEntity UpdateQuality(QualityEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 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_Quality
Set QualityName=:QualityName,
SalaryType=:SalaryType,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where QualityID=:QualityID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":QualityName",OracleDbType.NVarchar2,
dfEntity.QualityName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":QualityID",OracleDbType.Int32,
dfEntity.QualityID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
///
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationQuality(IDBTransaction trConn,
QualityEntity dfEntity, 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,
dfEntity.BeginAccountMonthStart,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_Quality
Where SalaryType = :SalaryType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批质量考核策略
///
/// 审批策略实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditQuality(QualityEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationQuality(oracleTrConn, dfEntity, 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_Quality
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where QualityID=:QualityID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":QualityID",OracleDbType.Int32,
dfEntity.QualityID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 停用质量考核策略
///
/// 停用实体
/// 用户基本信息
/// ServiceResultEntity返回影响行
public static ServiceResultEntity StopQuality(QualityEntity dfEntity, 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_Quality
Set ValueFlag = 0
Where QualityID=:QualityID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
dfEntity.QualityID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_QualityDetail
Where QualityID = :QualityID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
dfEntity.QualityID,ParameterDirection.Input),
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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
/// 明细数据源
/// 用户基本信息
/// ServiceResultEntity
public static ServiceResultEntity EditQualityDetail(int QualityID, DataSet dsSourse, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
StringBuilder sbSql = new StringBuilder();
#region 质量考核产品明细（增删改操作）
DataTable dtGoodsSourse = dsSourse.Tables[1];
foreach (DataRow drFor in dtGoodsSourse.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["GoodsID"] == DBNull.Value)
{
continue;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_QualityGoodsDetail(QualityID,JobsID,GoodsID,
ComplianceRate,IncrementSeed,IncrementAmount,
AccountID,CreateUserID,UpdateUserID)
Values(:QualityID,:JobsID,:GoodsID,
:ComplianceRate,:IncrementSeed,:IncrementAmount,
:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
new OracleParameter(":ComplianceRate",OracleDbType.Decimal,
drFor["ComplianceRate"],ParameterDirection.Input),
new OracleParameter(":IncrementSeed",OracleDbType.Decimal,
drFor["IncrementSeed"],ParameterDirection.Input),
new OracleParameter(":IncrementAmount",OracleDbType.Decimal,
drFor["IncrementAmount"],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.Modified) //修改
{
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_QualityGoodsDetail
Set ComplianceRate = :ComplianceRate,
IncrementSeed = :IncrementSeed,
IncrementAmount = :IncrementAmount,
UpdateUserID = :UpdateUserID
Where QualityID = :QualityID and JobsID = :JobsID and GoodsID = :GoodsID");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":ComplianceRate",OracleDbType.Decimal,
drFor["ComplianceRate"],ParameterDirection.Input),
new OracleParameter(":IncrementSeed",OracleDbType.Decimal,
drFor["IncrementSeed"],ParameterDirection.Input),
new OracleParameter(":IncrementAmount",OracleDbType.Decimal,
drFor["IncrementAmount"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID"],ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_QualityGoodsDetail
Where QualityID = :QualityID
and GoodsID = :GoodsID
and JobsID = :JobsID
");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":GoodsID",OracleDbType.Int32,
drFor["GoodsID",DataRowVersion.Original],ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
//删除质量考核产品明细数据（右边对应列数据）
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
}
}
#endregion
#region 质量考核明细（增删改操作）
DataTable dtSourse = dsSourse.Tables[0];
foreach (DataRow drFor in dtSourse.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["JobsId"] != DBNull.Value)
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_QualityDetail(QualityID,JobsID,BaseAmount,
IsEnd,AccountID,CreateUserID,UpdateUserID)
Values(:QualityID,:JobsID,:BaseAmount,
:IsEnd,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],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.Modified) //修改
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_QualityDetail
Set BaseAmount = :BaseAmount,
IsEnd = :IsEnd,
UpdateUserID = :UpdateUserID
Where QualityID = :QualityID and JobsID = :JobsID ");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_QualityDetail
Where QualityID = :QualityID
and JobsID = :JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
//删除质量考核产品明细数据（右边对应列数据）
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_QualityGoodsDetail
Where QualityID = :QualityID
and JobsID = :JobsID");
OracleParameter[] DeleteGoodsParas = new OracleParameter[] {
new OracleParameter(":QualityID",OracleDbType.Int32,
QualityID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteGoodsParas);
}
}
#endregion
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;
}
}
#endregion
#region 进度考核
///
/// 新建进度考核策略
///
/// 新建实体
/// 用户基本信息
///
/// ServiceResultEntity受影响行数
///
public static ServiceResultEntity AddProgress(ProgressEntity dfEntity, 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_progress_progressid.nextval from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
sbSql.Clear();
//添加缺陷扣罚策略主体信息
sbSql.Append(@"Insert into TP_TAT_Progress
(ProgressID,ProgressName,
SalaryType,
BeginAccountMonth,Remarks,
AuditStatus,AccountID,
CreateUserID,UpdateUserID)
Values
(:ProgressID,:ProgressName,
:SalaryType,:BeginAccountMonth,:Remarks,
:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":ProgressID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":ProgressName",OracleDbType.NVarchar2,
dfEntity.ProgressName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//如果是复制添加,那么还要将复制主体信息的明细全部添加
if (dfEntity.CopyId != 0)
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_ProgressDetail
(ProgressID,JobsID,BaseAmount,IsEnd,AccountID,CreateUserID,UpdateUserID)
Select :ProgressID,JobsID,
BaseAmount,IsEnd,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_ProgressDetail Where ProgressID = :CopyProgressID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":ProgressID",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(":CopyProgressID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
}
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 修改进度考核信息
///
/// 修改实体
/// 用户基本信息
/// ServiceResultEntity受影响行数
public static ServiceResultEntity UpdateProgress(ProgressEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 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_Progress
Set ProgressName=:ProgressName,
SalaryType=:SalaryType,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where ProgressID=:ProgressID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":ProgressName",OracleDbType.NVarchar2,
dfEntity.ProgressName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":ProgressID",OracleDbType.Int32,
dfEntity.ProgressID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
///
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationProgress(IDBTransaction trConn,
ProgressEntity dfEntity, 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,
dfEntity.BeginAccountMonthStart,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_Progress
Where SalaryType = :SalaryType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批进度考核策略
///
/// 审批策略实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditProgress(ProgressEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationProgress(oracleTrConn, dfEntity, 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_Progress
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where ProgressID=:ProgressID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":ProgressID",OracleDbType.Int32,
dfEntity.ProgressID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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 StopProgress(ProgressEntity dfEntity, 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_Progress
Set ValueFlag = 0
Where ProgressID=:ProgressID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":ProgressID",OracleDbType.Int32,
dfEntity.ProgressID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_ProgressDetail
Where ProgressID = :ProgressID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":ProgressID",OracleDbType.Int32,
dfEntity.ProgressID,ParameterDirection.Input),
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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
/// 明细数据源
/// 用户基本信息
/// ServiceResultEntity
public static ServiceResultEntity EditProgressDetail(int ProgressID, DataSet dsSourse, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
StringBuilder sbSql = new StringBuilder();
#region 行政考核明细（增删改操作）
DataTable dtSourse = dsSourse.Tables[0];
foreach (DataRow drFor in dtSourse.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_ProgressDetail(ProgressID,JobsID,BaseAmount,
IsEnd,AccountID,CreateUserID,UpdateUserID)
Values(:ProgressID,:JobsID,:BaseAmount,
:IsEnd,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":ProgressID",OracleDbType.Int32,
ProgressID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],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.Modified) //修改
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_ProgressDetail
Set BaseAmount = :BaseAmount,
IsEnd = :IsEnd,
UpdateUserID = :UpdateUserID
Where ProgressID = :ProgressID and JobsID = :JobsID ");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":ProgressID",OracleDbType.Int32,
ProgressID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_ProgressDetail
Where ProgressID = :ProgressID
and JobsID = :JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":ProgressID",OracleDbType.Int32,
ProgressID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
//删除质量考核产品明细数据（右边对应列数据）
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
}
}
#endregion
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;
}
}
#endregion
#region 行政考核
///
/// 新建行政考核策略
///
/// 新建实体
/// 用户基本信息
///
/// 受影响行数
///
public static ServiceResultEntity AddAdministration(AdministrationEntity dfEntity, 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_administration_id.nextval from dual");
int entityId = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sbSql.ToString()));
sbSql.Clear();
//添加缺陷扣罚策略主体信息
sbSql.Append(@"Insert into TP_TAT_Administration
(AdministrationID,AdministrationName,
SalaryType,AdministrationType,
BeginAccountMonth,Remarks,
AuditStatus,AccountID,
CreateUserID,UpdateUserID)
Values
(:AdministrationID,:AdministrationName,:SalaryType,:AdministrationType,
:BeginAccountMonth,:Remarks,:AuditStatus,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",OracleDbType.Int32,
entityId,ParameterDirection.Input),
new OracleParameter(":AdministrationName",OracleDbType.NVarchar2,
dfEntity.AdministrationName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":AdministrationType",OracleDbType.Int32,
dfEntity.AdministrationType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.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(), DFParas);
//如果是复制添加,那么还要将复制主体信息的明细全部添加
if (dfEntity.CopyId != 0)
{
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_AdminDetail
(AdministrationID,JobsID,BaseAmount,IsEnd,AccountID,CreateUserID,UpdateUserID)
Select :AdministrationID,JobsID,
BaseAmount,IsEnd,
AccountID,:CreateUserID,:UpdateUserID
From TP_TAT_AdminDetail Where AdministrationID = :CopyAdministrationID");
OracleParameter[] CopyParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",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(":CopyAdministrationID",OracleDbType.Int32,
dfEntity.CopyId,ParameterDirection.Input),
};
CopyCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), CopyParas);
}
//如果插入失败则回滚事务并关闭
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 UpdateAdministration(AdministrationEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 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_Administration
Set AdministrationName=:AdministrationName,
SalaryType=:SalaryType,
AdministrationType=:AdministrationType,
BeginAccountMonth=:BeginAccountMonth,
Remarks=:Remarks,
UpdateUserID=:UpdateUserID
Where AdministrationID=:AdministrationID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AdministrationName",OracleDbType.NVarchar2,
dfEntity.AdministrationName,ParameterDirection.Input),
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":AdministrationType",OracleDbType.Int32,
dfEntity.AdministrationType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":Remarks",OracleDbType.NVarchar2,
dfEntity.Remarks,ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AdministrationID",OracleDbType.Int32,
dfEntity.AdministrationID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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;
}
}
///
/// 验证数据有效性（是否可通过审批）
///
/// 数据库事务连接
/// 当前操作实体
/// 用户基本信息
///
/// 0为可进行相应操作
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static int ValidationAdministration(IDBTransaction trConn,
AdministrationEntity dfEntity, 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,
dfEntity.BeginAccountMonthStart,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_Administration
Where SalaryType = :SalaryType
and AdministrationType=:AdministrationType
and BeginAccountMonth = :BeginAccountMonth
and AccountID=:AccountID
and AuditStatus = 1
and ValueFlag = 1");
OracleParameter[] CDParas = new OracleParameter[] {
new OracleParameter(":SalaryType",OracleDbType.Int32,
dfEntity.SalaryType,ParameterDirection.Input),
new OracleParameter(":AdministrationType",OracleDbType.Int32,
dfEntity.AdministrationType,ParameterDirection.Input),
new OracleParameter(":BeginAccountMonth",OracleDbType.Date,
dfEntity.BeginAccountMonthStart,ParameterDirection.Input),
new OracleParameter(":AccountID",OracleDbType.Int32,
userInfo.AccountID,ParameterDirection.Input),
};
DataTable dtCheckDef = trConn.GetSqlResultToDt(sbSql.ToString(), CDParas);
if (dtCheckDef.Rows.Count != 0) //如果存在同类别同开始日期策略，False
{
return -998;
}
//符合以上条件方可通过验证
return 0;
}
catch (Exception ex)
{
throw ex;
}
}
///
/// 审批行政考核策略
///
/// 审批策略实体
/// 用户基本信息
///
/// 返回受影响行数
/// -999为策略开始月小于系统结算月,
/// -998为存在同类别同开始日期策略.
///
public static ServiceResultEntity AuditAdministration(AdministrationEntity dfEntity, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
//首先验证即将通过审批的实体的数据合法性
if (dfEntity.AuditStatus == Convert.ToInt32(Constant.AuditStatus.Agree))
{
RowCount = ValidationAdministration(oracleTrConn, dfEntity, 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_Administration
Set AuditStatus=:AuditStatus,
Auditor=:Auditor,
AuditlDate=:AuditlDate
Where AdministrationID=:AdministrationID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AuditStatus",OracleDbType.Int32,
dfEntity.AuditStatus,ParameterDirection.Input),
new OracleParameter(":Auditor",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AuditlDate",OracleDbType.Date,
accountDate,ParameterDirection.Input),
new OracleParameter(":AdministrationID",OracleDbType.Int32,
dfEntity.AdministrationID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
//如果插入失败则回滚事务并关闭
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 StopAdministration(AdministrationEntity dfEntity, 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_Administration
Set ValueFlag = 0
Where AdministrationID=:AdministrationID
And OPTimeStamp=:OPTimeStamp");
OracleParameter[] DFParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",OracleDbType.Int32,
dfEntity.AdministrationID,ParameterDirection.Input),
new OracleParameter(":OPTimeStamp",OracleDbType.TimeStamp,
dfEntity.OPTimeStamp,ParameterDirection.Input),
};
//连接数据库并返回结果
RowCount = oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFParas);
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_AdminDetail
Where AdministrationID = :AdministrationID");
OracleParameter[] DFDParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",OracleDbType.Int32,
dfEntity.AdministrationID,ParameterDirection.Input),
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DFDParas);
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 EditAdminDetail(int AdministrationID, DataSet dsSourse, SUserInfo userInfo)
{
int RowCount = 0;
IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
ServiceResultEntity srEntity = new ServiceResultEntity();
try
{
oracleTrConn.Connect();
StringBuilder sbSql = new StringBuilder();
#region 行政考核明细（增删改操作）
DataTable dtSourse = dsSourse.Tables[0];
foreach (DataRow drFor in dtSourse.Rows)
{
if (drFor.RowState == DataRowState.Added) //添加
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Insert into TP_TAT_AdminDetail(AdministrationID,JobsID,BaseAmount,
IsEnd,AccountID,CreateUserID,UpdateUserID)
Values(:AdministrationID,:JobsID,:BaseAmount,
:IsEnd,:AccountID,:CreateUserID,:UpdateUserID)");
OracleParameter[] InsertParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",OracleDbType.Int32,
AdministrationID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input),
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],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.Modified) //修改
{
if (drFor["BaseAmount"] == DBNull.Value)
{
drFor["BaseAmount"] = 0;
}
if (drFor["IsEnd"] == DBNull.Value)
{
drFor["IsEnd"] = 0;
}
sbSql.Clear();
sbSql.Append(@"Update TP_TAT_AdminDetail
Set BaseAmount = :BaseAmount,
IsEnd = :IsEnd,
UpdateUserID = :UpdateUserID
Where AdministrationID = :AdministrationID and JobsID = :JobsID ");
OracleParameter[] UpdateParas = new OracleParameter[] {
new OracleParameter(":BaseAmount",OracleDbType.Decimal,
drFor["BaseAmount"],ParameterDirection.Input),
new OracleParameter(":IsEnd",OracleDbType.Int32,
drFor["IsEnd"],ParameterDirection.Input),
new OracleParameter(":UpdateUserID",OracleDbType.Int32,
userInfo.UserID,ParameterDirection.Input),
new OracleParameter(":AdministrationID",OracleDbType.Int32,
AdministrationID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId"],ParameterDirection.Input)
};
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), UpdateParas);
}
else if (drFor.RowState == DataRowState.Deleted) //删除
{
sbSql.Clear();
sbSql.Append(@"Delete From TP_TAT_AdminDetail
Where AdministrationID = :AdministrationID
and JobsID = :JobsID");
OracleParameter[] DeleteParas = new OracleParameter[] {
new OracleParameter(":AdministrationID",OracleDbType.Int32,
AdministrationID,ParameterDirection.Input),
new OracleParameter(":JobsID",OracleDbType.Int32,
drFor["JobsId",DataRowVersion.Original],ParameterDirection.Input)
};
//删除质量考核产品明细数据（右边对应列数据）
RowCount += oracleTrConn.ExecuteNonQuery(sbSql.ToString(), DeleteParas);
}
}
#endregion
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;
}
}
#endregion
}
}