public void UpdateCustomerVATInfo(CustomerVATInfo oParam)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (oParam != null)
{
//string sql = "select top 1 sysno from customer_vatinfo where sysno <>" + oParam.SysNo + " and BankAccount=" +Util.ToSqlString(oParam.BankAccount);
//DataSet ds = SqlHelper.ExecuteDataSet(sql);
//if (Util.HasMoreRow(ds))
// throw new BizException("�������˺��Ѿ���ʹ�ã���ȷ���˺���Ϣ");
if (1 != new CustomerDac().Update(oParam))
throw new BizException("���¿ͻ���Ʊ��Ϣʧ��");
}
else
{
throw new BizException("������ʧ��������");
}
scope.Complete();
}
}
private void map(CustomerVATInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.CustomerSysNo = Util.TrimIntNull(tempdr["CustomerSysNo"]);
oParam.CompanyName = Util.TrimNull(tempdr["CompanyName"]);
oParam.TaxNum = Util.TrimNull(tempdr["TaxNum"]);
oParam.CompanyAddress = Util.TrimNull(tempdr["CompanyAddress"]);
oParam.CompanyPhone = Util.TrimNull(tempdr["CompanyPhone"]);
oParam.BankInfo = Util.TrimNull(tempdr["BankInfo"]);
oParam.BankAccount = Util.TrimNull(tempdr["BankAccount"]);
oParam.Image1 = Util.TrimNull(tempdr["Image1"]);
oParam.Image2 = Util.TrimNull(tempdr["Image2"]);
oParam.Image3 = Util.TrimNull(tempdr["Image3"]);
oParam.Image4 = Util.TrimNull(tempdr["Image4"]);
oParam.Memo = Util.TrimNull(tempdr["Memo"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.IsDefault = Util.TrimIntNull(tempdr["IsDefault"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
/// <summary>
/// ��Ա�ȼ�����(�����ͻ��������ù�˾��Ϣ)
/// </summary>
/// <param name="customerHash">Ҫ���µĿͻ���Ϣ</param>
/// <param name="customerVATInfo">��Ʊ��Ϣ(����Ʊ��Ϊnull)</param>
public void SetCustomer(Hashtable customerHash, CustomerVATInfo customerVATInfo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
if (customerHash.ContainsKey("CustomerType"))
{
int customerType = Util.TrimIntNull(customerHash["CustomerType"].ToString());
if (customerType == (int)AppEnum.CustomerType.Enterprice || customerType == (int)AppEnum.CustomerType.VIP)
{
if (customerVATInfo != null)
{
CustomerVATInfo oldVat = LoadCustomerVATInfo(AppConst.IntNull, customerVATInfo.CustomerSysNo, true);
if (oldVat == null)
{
InsertCustomerVATInfo(customerVATInfo);
}
else
{
customerVATInfo.SysNo = oldVat.SysNo;
UpdateCustomerVATInfo(customerVATInfo);
}
}
}
}
Update(customerHash);
scope.Complete();
}
}
/// <summary>
/// ���ؿͻ���Ʊ��Ϣ
/// </summary>
/// <param name="sysNo">ϵͳ���</param>
/// <param name="customerSysNo">�ͻ����</param>
/// <param name="isOnlyLoadDefault">�Ƿ�ֻ����Ĭ�ϵ���Ʊ</param>
/// <returns></returns>
public CustomerVATInfo LoadCustomerVATInfo(int sysNo, int customerSysNo, bool isOnlyLoadDefault)
{
string sql = " Select top 1 * From Customer_VATInfo Where 1=1 ";
if (sysNo != AppConst.IntNull)
{
sql += " AND SysNo = " + sysNo;
}
if (customerSysNo != AppConst.IntNull)
{
sql += " AND CustomerSysNo =" + customerSysNo;
}
if (isOnlyLoadDefault)
{
sql += " AND IsDefault = " + (int)AppEnum.YNStatus.Yes;
}
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
{
return null;
}
else
{
CustomerVATInfo oInfo = new CustomerVATInfo();
map(oInfo, ds.Tables[0].Rows[0]);
return oInfo;
}
}
public void InsertCustomerVATInfo(CustomerVATInfo oParam)
{
if (1 != new CustomerDac().Insert(oParam))
throw new BizException("����ͻ���Ʊ��Ϣʧ��");
}
public int Update(CustomerVATInfo oParam)
{
string sql = @"UPDATE Customer_VATInfo SET
CustomerSysNo=@CustomerSysNo, CompanyName=@CompanyName,
TaxNum=@TaxNum, CompanyAddress=@CompanyAddress,
CompanyPhone=@CompanyPhone, BankInfo=@BankInfo,
BankAccount=@BankAccount, Image1=@Image1,
Image2=@Image2, Image3=@Image3,
Image4=@Image4, Memo=@Memo,
CreateTime=@CreateTime, IsDefault=@IsDefault,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int, 4);
SqlParameter paramCompanyName = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 100);
SqlParameter paramTaxNum = new SqlParameter("@TaxNum", SqlDbType.NVarChar, 20);
SqlParameter paramCompanyAddress = new SqlParameter("@CompanyAddress", SqlDbType.NVarChar, 200);
SqlParameter paramCompanyPhone = new SqlParameter("@CompanyPhone", SqlDbType.NVarChar, 50);
SqlParameter paramBankInfo = new SqlParameter("@BankInfo", SqlDbType.NVarChar, 100);
SqlParameter paramBankAccount = new SqlParameter("@BankAccount", SqlDbType.NVarChar, 100);
SqlParameter paramImage1 = new SqlParameter("@Image1", SqlDbType.NVarChar, 100);
SqlParameter paramImage2 = new SqlParameter("@Image2", SqlDbType.NVarChar, 100);
SqlParameter paramImage3 = new SqlParameter("@Image3", SqlDbType.NVarChar, 100);
SqlParameter paramImage4 = new SqlParameter("@Image4", SqlDbType.NVarChar, 100);
SqlParameter paramMemo = new SqlParameter("@Memo", SqlDbType.NVarChar, 200);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramIsDefault = new SqlParameter("@IsDefault", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
if (oParam.SysNo != AppConst.IntNull)
paramSysNo.Value = oParam.SysNo;
else
paramSysNo.Value = System.DBNull.Value;
if (oParam.CustomerSysNo != AppConst.IntNull)
paramCustomerSysNo.Value = oParam.CustomerSysNo;
else
paramCustomerSysNo.Value = System.DBNull.Value;
if (oParam.CompanyName != AppConst.StringNull)
paramCompanyName.Value = oParam.CompanyName;
else
paramCompanyName.Value = System.DBNull.Value;
if (oParam.TaxNum != AppConst.StringNull)
paramTaxNum.Value = oParam.TaxNum;
else
paramTaxNum.Value = System.DBNull.Value;
if (oParam.CompanyAddress != AppConst.StringNull)
paramCompanyAddress.Value = oParam.CompanyAddress;
else
paramCompanyAddress.Value = System.DBNull.Value;
if (oParam.CompanyPhone != AppConst.StringNull)
paramCompanyPhone.Value = oParam.CompanyPhone;
else
paramCompanyPhone.Value = System.DBNull.Value;
if (oParam.BankInfo != AppConst.StringNull)
paramBankInfo.Value = oParam.BankInfo;
else
paramBankInfo.Value = System.DBNull.Value;
if (oParam.BankAccount != AppConst.StringNull)
paramBankAccount.Value = oParam.BankAccount;
else
paramBankAccount.Value = System.DBNull.Value;
if (oParam.Image1 != AppConst.StringNull)
paramImage1.Value = oParam.Image1;
else
paramImage1.Value = System.DBNull.Value;
if (oParam.Image2 != AppConst.StringNull)
paramImage2.Value = oParam.Image2;
else
paramImage2.Value = System.DBNull.Value;
if (oParam.Image3 != AppConst.StringNull)
paramImage3.Value = oParam.Image3;
else
paramImage3.Value = System.DBNull.Value;
if (oParam.Image4 != AppConst.StringNull)
paramImage4.Value = oParam.Image4;
else
paramImage4.Value = System.DBNull.Value;
if (oParam.Memo != AppConst.StringNull)
paramMemo.Value = oParam.Memo;
else
paramMemo.Value = System.DBNull.Value;
if (oParam.CreateTime != AppConst.DateTimeNull)
paramCreateTime.Value = oParam.CreateTime;
else
paramCreateTime.Value = System.DBNull.Value;
if (oParam.IsDefault != AppConst.IntNull)
paramIsDefault.Value = oParam.IsDefault;
else
paramIsDefault.Value = System.DBNull.Value;
if (oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramCustomerSysNo);
cmd.Parameters.Add(paramCompanyName);
cmd.Parameters.Add(paramTaxNum);
cmd.Parameters.Add(paramCompanyAddress);
cmd.Parameters.Add(paramCompanyPhone);
cmd.Parameters.Add(paramBankInfo);
cmd.Parameters.Add(paramBankAccount);
cmd.Parameters.Add(paramImage1);
cmd.Parameters.Add(paramImage2);
cmd.Parameters.Add(paramImage3);
cmd.Parameters.Add(paramImage4);
cmd.Parameters.Add(paramMemo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramIsDefault);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int Insert(CustomerVATInfo oParam)
{
string sql = @"INSERT INTO Customer_VATInfo
(
CustomerSysNo, CompanyName, TaxNum, CompanyAddress,
CompanyPhone, BankInfo, BankAccount, Image1,
Image2, Image3, Image4, Memo,
CreateTime, IsDefault, Status
)
VALUES (
@CustomerSysNo, @CompanyName, @TaxNum, @CompanyAddress,
@CompanyPhone, @BankInfo, @BankAccount, @Image1,
@Image2, @Image3, @Image4, @Memo,
@CreateTime, @IsDefault, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramCustomerSysNo = new SqlParameter("@CustomerSysNo", SqlDbType.Int, 4);
SqlParameter paramCompanyName = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 100);
SqlParameter paramTaxNum = new SqlParameter("@TaxNum", SqlDbType.NVarChar, 20);
SqlParameter paramCompanyAddress = new SqlParameter("@CompanyAddress", SqlDbType.NVarChar, 200);
SqlParameter paramCompanyPhone = new SqlParameter("@CompanyPhone", SqlDbType.NVarChar, 50);
SqlParameter paramBankInfo = new SqlParameter("@BankInfo", SqlDbType.NVarChar, 100);
SqlParameter paramBankAccount = new SqlParameter("@BankAccount", SqlDbType.NVarChar, 100);
SqlParameter paramImage1 = new SqlParameter("@Image1", SqlDbType.NVarChar, 100);
SqlParameter paramImage2 = new SqlParameter("@Image2", SqlDbType.NVarChar, 100);
SqlParameter paramImage3 = new SqlParameter("@Image3", SqlDbType.NVarChar, 100);
SqlParameter paramImage4 = new SqlParameter("@Image4", SqlDbType.NVarChar, 100);
SqlParameter paramMemo = new SqlParameter("@Memo", SqlDbType.NVarChar, 200);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramIsDefault = new SqlParameter("@IsDefault", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.CustomerSysNo != AppConst.IntNull)
paramCustomerSysNo.Value = oParam.CustomerSysNo;
else
paramCustomerSysNo.Value = System.DBNull.Value;
if (oParam.CompanyName != AppConst.StringNull)
paramCompanyName.Value = oParam.CompanyName;
else
paramCompanyName.Value = System.DBNull.Value;
if (oParam.TaxNum != AppConst.StringNull)
paramTaxNum.Value = oParam.TaxNum;
else
paramTaxNum.Value = System.DBNull.Value;
if (oParam.CompanyAddress != AppConst.StringNull)
paramCompanyAddress.Value = oParam.CompanyAddress;
else
paramCompanyAddress.Value = System.DBNull.Value;
if (oParam.CompanyPhone != AppConst.StringNull)
paramCompanyPhone.Value = oParam.CompanyPhone;
else
paramCompanyPhone.Value = System.DBNull.Value;
if (oParam.BankInfo != AppConst.StringNull)
paramBankInfo.Value = oParam.BankInfo;
else
paramBankInfo.Value = System.DBNull.Value;
if (oParam.BankAccount != AppConst.StringNull)
paramBankAccount.Value = oParam.BankAccount;
else
paramBankAccount.Value = System.DBNull.Value;
if (oParam.Image1 != AppConst.StringNull)
paramImage1.Value = oParam.Image1;
else
paramImage1.Value = System.DBNull.Value;
if (oParam.Image2 != AppConst.StringNull)
paramImage2.Value = oParam.Image2;
else
paramImage2.Value = System.DBNull.Value;
if (oParam.Image3 != AppConst.StringNull)
paramImage3.Value = oParam.Image3;
else
paramImage3.Value = System.DBNull.Value;
if (oParam.Image4 != AppConst.StringNull)
paramImage4.Value = oParam.Image4;
else
paramImage4.Value = System.DBNull.Value;
if (oParam.Memo != AppConst.StringNull)
paramMemo.Value = oParam.Memo;
else
paramMemo.Value = System.DBNull.Value;
if (oParam.CreateTime != AppConst.DateTimeNull)
paramCreateTime.Value = oParam.CreateTime;
else
paramCreateTime.Value = System.DBNull.Value;
if (oParam.IsDefault != AppConst.IntNull)
paramIsDefault.Value = oParam.IsDefault;
else
paramIsDefault.Value = System.DBNull.Value;
if (oParam.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramCustomerSysNo);
cmd.Parameters.Add(paramCompanyName);
cmd.Parameters.Add(paramTaxNum);
cmd.Parameters.Add(paramCompanyAddress);
cmd.Parameters.Add(paramCompanyPhone);
cmd.Parameters.Add(paramBankInfo);
cmd.Parameters.Add(paramBankAccount);
cmd.Parameters.Add(paramImage1);
cmd.Parameters.Add(paramImage2);
cmd.Parameters.Add(paramImage3);
cmd.Parameters.Add(paramImage4);
cmd.Parameters.Add(paramMemo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramIsDefault);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}