You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

250 lines
9.8 KiB
C#

11 months ago
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using DSWeb.Models;
using System.Data.SqlClient;
using WebSqlHelper;
using System.Collections.Generic;
namespace DSWeb.EntityDA
{
public class AmendDA
{
private const string PARM_AMEND_GID = "@gid";
private const string PARM_AMEND_BSNO = "@bsno";
private const string PARM_AMEND_PARENT_ID = "@parent_id";
private const string PARM_AMEND_FEE_STATUS = "@fee_status";
private const string PARM_AMEND_ACC_DATE = "@acc_date";
private const string PARM_AMEND_CREATE_USER = "@create_user";
private const string PARM_AMEND_CREATE_TIME = "@create_time";
private const string PARM_AMEND_REASON = "@reason";
private const string PARM_AMEND_REMARKS = "@remarks";
private const string SQL_SELECT_AMEND_BY_PARENT_ID = "SELECT GID, BSNO, PARENTID, FEESTATUS, ACCDATE, CREATEUSER, CREATETIME, REASON, REMARKS FROM op_amend WHERE PARENTID = @parent_id";
private const string SQL_SELECT_AMEND_BY_GID = "SELECT GID, BSNO, PARENTID, FEESTATUS, ACCDATE, CREATEUSER, CREATETIME, REASON, REMARKS FROM op_amend WHERE GID = @gid";
private const string SQL_SELECT_AMEND_BY_BSNO = "SELECT GID, BSNO, PARENTID, FEESTATUS, ACCDATE, CREATEUSER, CREATETIME, REASON, REMARKS FROM op_amend WHERE BSNO = @bsno";
private const string SQL_INSERT_AMEND = "INSERT INTO op_amend(GID, BSNO, PARENTID, FEESTATUS, CREATEUSER, CREATETIME, REASON, REMARKS) VALUES(@gid,@bsno,@parent_id,@fee_status,@create_user,getdate(),@reason,@remarks)";
#region 根据费用更改单GID获取费用更改信息
/// <summary>
/// 根据费用更改单GID获取费用更改信息
/// </summary>
/// <param name="tempGID">费用更改单GID</param>
/// <returns>AmendEntity实体类</returns>
public AmendEntity GetAmendByGID(string tempGID)
{
AmendEntity amendEntity = null;
SqlParameter parm = new SqlParameter(PARM_AMEND_GID, SqlDbType.VarChar, 36);
parm.Value = tempGID;
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_AMEND_BY_GID, parm))
{
try
{
while (sqlRead.Read())
{
amendEntity = new AmendEntity();
if (!sqlRead.IsDBNull(0))
{
amendEntity.GID = sqlRead.GetString(0);
}
if (!sqlRead.IsDBNull(1))
{
amendEntity.BSNO = sqlRead.GetString(1);
}
if (!sqlRead.IsDBNull(2))
{
amendEntity.ParentID = sqlRead.GetString(2);
}
if (!sqlRead.IsDBNull(3))
{
amendEntity.FeeStatus = sqlRead.GetInt32(3);
}
if (!sqlRead.IsDBNull(4))
{
amendEntity.ACCDate = sqlRead.GetDateTime(4);
}
if (!sqlRead.IsDBNull(5))
{
amendEntity.CreateUser = sqlRead.GetString(5);
}
if (!sqlRead.IsDBNull(6))
{
amendEntity.CreateTime = sqlRead.GetDateTime(6);
}
if (!sqlRead.IsDBNull(7))
{
amendEntity.Reason = sqlRead.GetString(7);
}
if (!sqlRead.IsDBNull(8))
{
amendEntity.Remarks = sqlRead.GetString(8);
}
}
}
catch (Exception execError)
{
throw execError;
}
}
return amendEntity;
}
#endregion
#region 根据业务更改单 ParentID值获取实体类组
/// <summary>
/// 根据业务更改单 ParentID值获取实体类组
/// </summary>
/// <param name="tempParentID">ParentID</param>
/// <returns>返回IList AmendEntity实体类组</returns>
public IList<AmendEntity> GetAmendsByGroupID(string tempParentID)
{
IList<AmendEntity> amendEntities = new List<AmendEntity>();
SqlParameter parm = new SqlParameter(PARM_AMEND_PARENT_ID, SqlDbType.VarChar, 100);
parm.Value = tempParentID;
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_AMEND_BY_PARENT_ID, parm))
{
try
{
while (sqlRead.Read())
{
AmendEntity amendEntity = new AmendEntity();
if (!sqlRead.IsDBNull(0))
{
amendEntity.GID = sqlRead.GetString(0);
}
if (!sqlRead.IsDBNull(1))
{
amendEntity.BSNO = sqlRead.GetString(1);
}
if (!sqlRead.IsDBNull(2))
{
amendEntity.ParentID = sqlRead.GetString(2);
}
if (!sqlRead.IsDBNull(3))
{
amendEntity.FeeStatus = sqlRead.GetInt32(3);
}
if (!sqlRead.IsDBNull(4))
{
amendEntity.ACCDate = sqlRead.GetDateTime(4);
}
if (!sqlRead.IsDBNull(5))
{
amendEntity.CreateUser = sqlRead.GetString(5);
}
if (!sqlRead.IsDBNull(6))
{
amendEntity.CreateTime = sqlRead.GetDateTime(6);
}
if (!sqlRead.IsDBNull(7))
{
amendEntity.Reason = sqlRead.GetString(7);
}
if (!sqlRead.IsDBNull(8))
{
amendEntity.Remarks = sqlRead.GetString(8);
}
amendEntities.Add(amendEntity);
}
}
catch (Exception execError)
{
throw execError;
}
}
return amendEntities;
}
#endregion
public int InsertAmends(IList<AmendEntity> tempAmendEntities)
{
int result = 0;
//判断是否有值
if (tempAmendEntities[0].GID != null)
{
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
foreach (AmendEntity amend in tempAmendEntities)
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter(PARM_AMEND_GID,SqlDbType.VarChar,36),
new SqlParameter(PARM_AMEND_BSNO,SqlDbType.VarChar,100),
new SqlParameter(PARM_AMEND_PARENT_ID,SqlDbType.VarChar,36),
new SqlParameter(PARM_AMEND_FEE_STATUS,SqlDbType.Int),
//new SqlParameter(PARM_AMEND_ACC_DATE,SqlDbType.SmallDateTime),
new SqlParameter(PARM_AMEND_CREATE_USER,SqlDbType.VarChar,36),
//new SqlParameter(PARM_AMEND_CREATE_TIME,SqlDbType.DateTime),
new SqlParameter(PARM_AMEND_REASON,SqlDbType.VarChar,100),
new SqlParameter(PARM_AMEND_REMARKS,SqlDbType.VarChar,100)
};
parms[0].Value = amend.GID;
parms[1].Value = amend.BSNO;
parms[2].Value = amend.ParentID;
parms[3].Value = amend.FeeStatus;
//parms[4].Value = amend.ACCDate;
parms[4].Value = amend.CreateUser;
//parms[5].Value = amend.CreateTime;
parms[5].Value = amend.Reason;
parms[6].Value = amend.Remarks;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_INSERT_AMEND, parms);
}
sqlTran.Commit();
result = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
result = -1;//有异常,插入失败
sqlTran.Rollback();
result = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
}
return result;
}
}
}