|
|
|
|
|
using System;
|
|
|
|
|
|
using System.Data;
|
|
|
|
|
|
using System.Data.SqlClient;
|
|
|
|
|
|
using DSWeb.Models;
|
|
|
|
|
|
using WebSqlHelper;
|
|
|
|
|
|
|
|
|
|
|
|
namespace DSWeb.EntityDA
|
|
|
|
|
|
{
|
|
|
|
|
|
public class FeeTemplateDA
|
|
|
|
|
|
{
|
|
|
|
|
|
//code_fee_template
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_GID = "@gid";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_NAME = "@name";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DESCRIPTION = "@description";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_CREATE_USER = "@create_user";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_CREATE_TIME = "@create_time";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_OP_TYPE = "@op_type";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_FEE_TYPE = "@fee_type";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_MODIFIED_USER = "@modified_user";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_MODIFIED_TIME = "@modified_time";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_REMARK = "@remark";
|
|
|
|
|
|
|
|
|
|
|
|
//code_fee_templatedetail
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_GID = "@gid";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_TEMPLATE_ID = "@template_id";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_FEE_CODE = "@fee_code";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_FEE_NAME = "@fee_name";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_CUSTOMER_NAME = "@customer_name";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_CLIENT = "@client";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_UNIT = "@unit";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_CURRENCY = "@currency";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_UNIT_PRICE = "@unit_price";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_REMARK = "@remark";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_SORT = "@sort";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_CREATE_USER = "@create_user";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_CREATE_TIME = "@create_time";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_MODIFIED_USER = "@modified_user";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_MODIFIED_TIME = "@modified_time";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_FEE_TYPE = "@fee_type";
|
|
|
|
|
|
private const string PARM_FEE_TEMPLATE_DETAIL_EXCHANGE_RATE = "@exchange_rate";
|
|
|
|
|
|
|
|
|
|
|
|
//code_fee_template
|
|
|
|
|
|
public const string SQL_SELECT_FEE_TEMPLATE_BY_GID = " SELECT GID, OPTYPE, FEETYPE, NAME, DESCRIPTION, CREATEUSER, CREATETIME, MODIFIEDUSER, MODIFIEDTIME, REMARK,CORPID "
|
|
|
|
|
|
+ " FROM code_fee_template WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
public const string SQL_SELECT_FEE_TEMPLATE_ALL = " SELECT GID, OPTYPE, FEETYPE, NAME, DESCRIPTION, CREATEUSER, CREATETIME, MODIFIEDUSER, MODIFIEDTIME, REMARK,CORPID "
|
|
|
|
|
|
+ " FROM code_fee_template ";
|
|
|
|
|
|
|
|
|
|
|
|
public const string SQL_INSERT_FEE_TEMPLATE = " INSERT INTO code_fee_template(GID,NAME,DESCRIPTION,CREATEUSER,CREATETIME,OPTYPE,FEETYPE,REMARK,CORPID) "
|
|
|
|
|
|
+ " VALUES(@gid,@name,@description,@create_user,GETDATE(),@op_type,@fee_type,@remark,@CORPID) ";
|
|
|
|
|
|
|
|
|
|
|
|
public const string SQL_UPDATE_FEE_TEMPLATE = " UPDATE code_fee_template SET NAME = @name,DESCRIPTION = @description,MODIFIEDUSER = @modified_user,MODIFIEDTIME = GETDATE(),OPTYPE = @op_type,FEETYPE = @fee_type,REMARK = @remark "
|
|
|
|
|
|
+ " WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
private const string SQL_SELECT_FEE_TEMPLATE_COUNT_BY_GID = "SELECT COUNT(*) FROM code_fee_template WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
//code_fee_templatedetail
|
|
|
|
|
|
private const string SQL_INSERT_FEE_TEMPLATE_DETAIL = " INSERT INTO code_fee_templatedetail(GID,TEMPLATEID,FEECODE,FEENAME,CUSTOMERNAME,CLIENT,UNIT,CURRENCY,UNITPRICE,REMARK,SORT,CREATEUSER,CREATETIME,FEETYPE,EXCHANGERATE) "
|
|
|
|
|
|
+ " VALUES(@gid,@template_id,@fee_code,@fee_name,@customer_name,@client,@unit,@currency,@unit_price,@remark,@sort,@create_user,GETDATE(),@fee_type,@exchange_rate) ";
|
|
|
|
|
|
|
|
|
|
|
|
private const string SQL_UPDATE_FEE_TEMPLATE_DETAIL = " UPDATE code_fee_templatedetail SET FEECODE = @fee_code,FEENAME = @fee_name,CUSTOMERNAME = @customer_name,CLIENT = @client"
|
|
|
|
|
|
+ " UNIT = @unit,CURRENCY = @currency,UNITPRICE = @unit_price,REMRAK = @remark,SORT = @sort,MODIFIEDUSER = @modified_user,MODIFIEDTIME = @modified_time,FEETYPE = @fee_type,EXCHANGERATE = @exchange_rate WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
private const string SQL_SELECT_COUNT_BY_GID = "SELECT COUNT(*) FROM code_fee_templatedetail WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
private const string SQL_DELETE_FEE_TEMPLATE_BY_GID = " DELETE FROM code_fee_template WHERE GID = @gid ";
|
|
|
|
|
|
|
|
|
|
|
|
private const string SQL_DELETE_FEE_TEMPLATE_DETAIL_BY_TEMPLATE_ID = " DELETE FROM code_fee_templatedetail WHERE TEMPLATEID = @template_id";
|
|
|
|
|
|
|
|
|
|
|
|
#region 通过费用模板GID删除模板信息
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 通过费用模板GID删除模板信息
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
/// <param name="tempFeeTemplateID">费用模板GID</param>
|
|
|
|
|
|
/// <returns>值1表示删除成功 值不等于1表示删除失败</returns>
|
|
|
|
|
|
public int Delete(string tempFeeTemplateID)
|
|
|
|
|
|
{
|
|
|
|
|
|
int iResult = 0;
|
|
|
|
|
|
|
|
|
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
|
|
|
{
|
|
|
|
|
|
try
|
|
|
|
|
|
{
|
|
|
|
|
|
SqlParameter[] existParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36)
|
|
|
|
|
|
};
|
|
|
|
|
|
|
|
|
|
|
|
existParms[0].Value = tempFeeTemplateID;
|
|
|
|
|
|
|
|
|
|
|
|
int iState = 0;
|
|
|
|
|
|
|
|
|
|
|
|
iState = (int)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_COUNT_BY_GID, existParms);
|
|
|
|
|
|
//先查看当前费用模板主模板下是否已经添加了明细模板数据,如果有则应先删除费用模板明细再删除主模板信息
|
|
|
|
|
|
if (iState > 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
SqlParameter[] detailParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36)
|
|
|
|
|
|
};
|
|
|
|
|
|
|
|
|
|
|
|
detailParms[0].Value = tempFeeTemplateID;
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_DELETE_FEE_TEMPLATE_BY_GID, detailParms);
|
|
|
|
|
|
|
|
|
|
|
|
SqlParameter[] delParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36)
|
|
|
|
|
|
};
|
|
|
|
|
|
delParms[0].Value = tempFeeTemplateID;
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_DELETE_FEE_TEMPLATE_BY_GID, delParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
//如果主模块下未添加明细模板信息,则只执行删除主模板信息
|
|
|
|
|
|
SqlParameter[] delParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36)
|
|
|
|
|
|
};
|
|
|
|
|
|
delParms[0].Value = tempFeeTemplateID;
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_DELETE_FEE_TEMPLATE_BY_GID, delParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
//事务提交
|
|
|
|
|
|
sqlTran.Commit();
|
|
|
|
|
|
|
|
|
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
|
|
|
}
|
|
|
|
|
|
catch (Exception execError)
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
|
|
|
sqlTran.Rollback();
|
|
|
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
|
|
|
throw execError;
|
|
|
|
|
|
}
|
|
|
|
|
|
finally
|
|
|
|
|
|
{
|
|
|
|
|
|
SqlHelper.CloseConnection();
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
return iResult;
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 通过费用模板GID获取信息
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 通过费用模板GID获取信息
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
/// <param name="tempFeeTemplateGID">费用模板GID</param>
|
|
|
|
|
|
/// <returns>FeeTemplateEntity实体类</returns>
|
|
|
|
|
|
public FeeTemplateEntity GetFeeTemplateByGID(string tempFeeTemplateGID)
|
|
|
|
|
|
{
|
|
|
|
|
|
FeeTemplateEntity feeTemplateEntity = null;
|
|
|
|
|
|
SqlParameter parm = new SqlParameter(PARM_FEE_TEMPLATE_GID, SqlDbType.VarChar, 36);
|
|
|
|
|
|
parm.Value = tempFeeTemplateGID;
|
|
|
|
|
|
using (SqlDataReader sqlRead = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_FEE_TEMPLATE_BY_GID, parm))
|
|
|
|
|
|
{
|
|
|
|
|
|
try
|
|
|
|
|
|
{
|
|
|
|
|
|
while (sqlRead.Read())
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity = new FeeTemplateEntity();
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(0))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.GID = sqlRead.GetString(0);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(1))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.OpType = sqlRead.GetInt32(1);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(2))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.FeeType = sqlRead.GetInt32(2);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(3))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.Name = sqlRead.GetString(3);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(4))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.Description = sqlRead.GetString(4);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(5))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.CreateUser = sqlRead.GetString(5);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(6))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.CreateTime = sqlRead.GetDateTime(6);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(7))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.ModifiedUser = sqlRead.GetString(7);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(8))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.ModifiedTime = sqlRead.GetDateTime(8);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(9))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.Remark = sqlRead.GetString(9);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
if (!sqlRead.IsDBNull(10))
|
|
|
|
|
|
{
|
|
|
|
|
|
feeTemplateEntity.CORPID = sqlRead.GetString(10);
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
catch (Exception execError)
|
|
|
|
|
|
{
|
|
|
|
|
|
throw execError;
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
return feeTemplateEntity;
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 插入费用模板
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 插入费用模板
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
/// <param name="tempFeeTemplateEntity">实体类费用模板信息</param>
|
|
|
|
|
|
/// <returns>值1表示插入成功 值不等于1表示插入失败</returns>
|
|
|
|
|
|
public int InsertFeeTemplate(FeeTemplateEntity tempFeeTemplateEntity)
|
|
|
|
|
|
{
|
|
|
|
|
|
int iResult = 0;
|
|
|
|
|
|
|
|
|
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_NAME,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DESCRIPTION,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_CREATE_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_OP_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_REMARK,SqlDbType.VarChar,200),
|
|
|
|
|
|
new SqlParameter("@CORPID",SqlDbType.VarChar,36)
|
|
|
|
|
|
};
|
|
|
|
|
|
insertParms[0].Value = tempFeeTemplateEntity.GID;
|
|
|
|
|
|
insertParms[1].Value = tempFeeTemplateEntity.Name;
|
|
|
|
|
|
insertParms[2].Value = tempFeeTemplateEntity.Description;
|
|
|
|
|
|
insertParms[3].Value = tempFeeTemplateEntity.CreateUser;
|
|
|
|
|
|
insertParms[4].Value = tempFeeTemplateEntity.OpType;
|
|
|
|
|
|
insertParms[5].Value = tempFeeTemplateEntity.FeeType;
|
|
|
|
|
|
insertParms[6].Value = tempFeeTemplateEntity.Remark;
|
|
|
|
|
|
insertParms[7].Value = tempFeeTemplateEntity.CORPID;
|
|
|
|
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
|
|
|
{
|
|
|
|
|
|
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_FEE_TEMPLATE, insertParms);
|
|
|
|
|
|
if (existVal > 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = 1;
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = -1;//执行异常
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
return iResult;
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 更新费用模板
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 更新费用模板
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
/// <param name="tempFeeTemplateEntity">实体类费用模板信息</param>
|
|
|
|
|
|
/// <returns>值1表示更新成功 值不等于1表示更新失败</returns>
|
|
|
|
|
|
public int UpdateFeeTemplate(FeeTemplateEntity tempFeeTemplateEntity)
|
|
|
|
|
|
{
|
|
|
|
|
|
int iResult = 0;
|
|
|
|
|
|
|
|
|
|
|
|
SqlParameter[] updateParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_NAME,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DESCRIPTION,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_MODIFIED_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_OP_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_REMARK,SqlDbType.VarChar,200)
|
|
|
|
|
|
};
|
|
|
|
|
|
|
|
|
|
|
|
updateParms[0].Value = tempFeeTemplateEntity.GID;
|
|
|
|
|
|
updateParms[1].Value = tempFeeTemplateEntity.Name;
|
|
|
|
|
|
updateParms[2].Value = tempFeeTemplateEntity.Description;
|
|
|
|
|
|
updateParms[3].Value = tempFeeTemplateEntity.ModifiedUser;
|
|
|
|
|
|
updateParms[4].Value = tempFeeTemplateEntity.OpType;
|
|
|
|
|
|
updateParms[5].Value = tempFeeTemplateEntity.FeeType;
|
|
|
|
|
|
updateParms[6].Value = tempFeeTemplateEntity.Remark;
|
|
|
|
|
|
|
|
|
|
|
|
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
|
|
|
{
|
|
|
|
|
|
int existVal = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_FEE_TEMPLATE, updateParms);
|
|
|
|
|
|
if (existVal > 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = 1;
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = -1;//执行异常
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
return iResult;
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
public int SaveFeeTemplateAndDetail(FeeTemplateEntity tempFeeTemplateEntity)
|
|
|
|
|
|
{
|
|
|
|
|
|
int iResult = 0;
|
|
|
|
|
|
SqlParameter checkTemplateParm = new SqlParameter(PARM_FEE_TEMPLATE_GID, SqlDbType.VarChar, 36);
|
|
|
|
|
|
|
|
|
|
|
|
#region 插入费用模板
|
|
|
|
|
|
SqlParameter[] insertTemplateParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_NAME,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DESCRIPTION,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_CREATE_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_OP_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_REMARK,SqlDbType.VarChar,200)
|
|
|
|
|
|
};
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 更新费用模板
|
|
|
|
|
|
SqlParameter[] updateTemplateParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_GID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_NAME,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DESCRIPTION,SqlDbType.VarChar,150),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_MODIFIED_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_OP_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_REMARK,SqlDbType.VarChar,200)
|
|
|
|
|
|
};
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
SqlParameter checkParm = new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_GID, SqlDbType.VarChar, 36);
|
|
|
|
|
|
|
|
|
|
|
|
#region 插入费用模板明细
|
|
|
|
|
|
SqlParameter[] insertParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_GID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_TEMPLATE_ID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_CODE,SqlDbType.VarChar,50),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CUSTOMER_NAME,SqlDbType.VarChar,100),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CLIENT,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_UNIT,SqlDbType.VarChar,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CURRENCY,SqlDbType.VarChar,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_UNIT_PRICE,SqlDbType.Decimal,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_REMARK,SqlDbType.VarChar,200),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_SORT,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CREATE_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_EXCHANGE_RATE,SqlDbType.Decimal,23)
|
|
|
|
|
|
};
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 更新费用模板明细
|
|
|
|
|
|
SqlParameter[] updateParms = new SqlParameter[] {
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_TEMPLATE_ID,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_CODE,SqlDbType.VarChar,50),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_NAME,SqlDbType.VarChar,100),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CUSTOMER_NAME,SqlDbType.VarChar,100),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CLIENT,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_UNIT,SqlDbType.VarChar,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_CURRENCY,SqlDbType.VarChar,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_UNIT_PRICE,SqlDbType.Decimal,20),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_REMARK,SqlDbType.VarChar,200),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_SORT,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_MODIFIED_USER,SqlDbType.VarChar,36),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_FEE_TYPE,SqlDbType.Int),
|
|
|
|
|
|
new SqlParameter(PARM_FEE_TEMPLATE_DETAIL_EXCHANGE_RATE,SqlDbType.Decimal,23)
|
|
|
|
|
|
};
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
|
|
|
|
|
|
{
|
|
|
|
|
|
try
|
|
|
|
|
|
{
|
|
|
|
|
|
int checkTemplateState = (int)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_FEE_TEMPLATE_COUNT_BY_GID, checkTemplateParm);
|
|
|
|
|
|
|
|
|
|
|
|
if (checkTemplateState == 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
insertTemplateParms[0].Value = tempFeeTemplateEntity.GID;
|
|
|
|
|
|
insertTemplateParms[1].Value = tempFeeTemplateEntity.Name;
|
|
|
|
|
|
insertTemplateParms[2].Value = tempFeeTemplateEntity.Description;
|
|
|
|
|
|
insertTemplateParms[3].Value = tempFeeTemplateEntity.CreateUser;
|
|
|
|
|
|
insertTemplateParms[4].Value = tempFeeTemplateEntity.OpType;
|
|
|
|
|
|
insertTemplateParms[5].Value = tempFeeTemplateEntity.FeeType;
|
|
|
|
|
|
insertTemplateParms[6].Value = tempFeeTemplateEntity.Remark;
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_TEMPLATE, insertTemplateParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
updateTemplateParms[0].Value = tempFeeTemplateEntity.GID;
|
|
|
|
|
|
updateTemplateParms[1].Value = tempFeeTemplateEntity.Name;
|
|
|
|
|
|
updateTemplateParms[2].Value = tempFeeTemplateEntity.Description;
|
|
|
|
|
|
updateTemplateParms[3].Value = tempFeeTemplateEntity.ModifiedUser;
|
|
|
|
|
|
updateTemplateParms[4].Value = tempFeeTemplateEntity.OpType;
|
|
|
|
|
|
updateTemplateParms[5].Value = tempFeeTemplateEntity.FeeType;
|
|
|
|
|
|
updateTemplateParms[6].Value = tempFeeTemplateEntity.Remark;
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_TEMPLATE_DETAIL, updateTemplateParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
foreach (FeeTemplateDetailEntity detailEntity in tempFeeTemplateEntity.FeeTemplateDetailEntity)
|
|
|
|
|
|
{
|
|
|
|
|
|
checkParm.Value = detailEntity.GID;
|
|
|
|
|
|
|
|
|
|
|
|
int checkState = (int)SqlHelper.ExecuteScalar(sqlTran, CommandType.Text, SQL_SELECT_COUNT_BY_GID, checkParm);
|
|
|
|
|
|
|
|
|
|
|
|
//如果费用模板明细GID,表中不存在,则执行插入操作,否则执行更新操作
|
|
|
|
|
|
if (checkState == 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
insertParms[0].Value = detailEntity.GID;
|
|
|
|
|
|
insertParms[1].Value = detailEntity.TemplateID;
|
|
|
|
|
|
insertParms[2].Value = detailEntity.FeeCode;
|
|
|
|
|
|
insertParms[3].Value = detailEntity.FeeName;
|
|
|
|
|
|
insertParms[4].Value = detailEntity.CustomerName;
|
|
|
|
|
|
insertParms[5].Value = detailEntity.Client;
|
|
|
|
|
|
insertParms[6].Value = detailEntity.Unit;
|
|
|
|
|
|
insertParms[7].Value = detailEntity.Currency;
|
|
|
|
|
|
insertParms[8].Value = detailEntity.UnitPrice;
|
|
|
|
|
|
insertParms[9].Value = detailEntity.Remark;
|
|
|
|
|
|
insertParms[10].Value = detailEntity.Sort;
|
|
|
|
|
|
insertParms[11].Value = detailEntity.CreateUser;
|
|
|
|
|
|
insertParms[12].Value = detailEntity.FeeType;
|
|
|
|
|
|
insertParms[13].Value = detailEntity.ExchangeRate;
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT_FEE_TEMPLATE_DETAIL, insertParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
updateParms[0].Value = detailEntity.GID;
|
|
|
|
|
|
updateParms[1].Value = detailEntity.TemplateID;
|
|
|
|
|
|
updateParms[2].Value = detailEntity.FeeCode;
|
|
|
|
|
|
updateParms[3].Value = detailEntity.FeeName;
|
|
|
|
|
|
updateParms[4].Value = detailEntity.CustomerName;
|
|
|
|
|
|
updateParms[5].Value = detailEntity.Client;
|
|
|
|
|
|
updateParms[6].Value = detailEntity.Unit;
|
|
|
|
|
|
updateParms[7].Value = detailEntity.Currency;
|
|
|
|
|
|
updateParms[8].Value = detailEntity.UnitPrice;
|
|
|
|
|
|
updateParms[9].Value = detailEntity.Remark;
|
|
|
|
|
|
updateParms[10].Value = detailEntity.Sort;
|
|
|
|
|
|
updateParms[11].Value = detailEntity.ModifiedUser;
|
|
|
|
|
|
updateParms[12].Value = detailEntity.FeeType;
|
|
|
|
|
|
updateParms[13].Value = detailEntity.ExchangeRate;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE_FEE_TEMPLATE_DETAIL, updateParms);
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
//事务提交
|
|
|
|
|
|
sqlTran.Commit();
|
|
|
|
|
|
|
|
|
|
|
|
iResult = 1;//状态为1表示插入成功
|
|
|
|
|
|
}
|
|
|
|
|
|
catch (Exception execError)
|
|
|
|
|
|
{
|
|
|
|
|
|
iResult = -1;//有异常,插入失败
|
|
|
|
|
|
sqlTran.Rollback();
|
|
|
|
|
|
iResult = -2;//插入异常,事务已回滚成功
|
|
|
|
|
|
throw execError;
|
|
|
|
|
|
}
|
|
|
|
|
|
finally
|
|
|
|
|
|
{
|
|
|
|
|
|
SqlHelper.CloseConnection();
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
return iResult;
|
|
|
|
|
|
}
|
|
|
|
|
|
#region 获取SQL语句查询数据集
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 获取SQL语句查询数据集
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
/// <param name="strSql"></param>
|
|
|
|
|
|
/// <returns></returns>
|
|
|
|
|
|
public DataSet GetExcuteSql(string strSql)
|
|
|
|
|
|
{
|
|
|
|
|
|
DataSet tempSet = new DataSet();
|
|
|
|
|
|
|
|
|
|
|
|
tempSet = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql);
|
|
|
|
|
|
return tempSet;
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
