public List<ReceivableAccount> GetReceivableAccountByCompanyId(int compId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId)
};
List<ReceivableAccount> result = new List<ReceivableAccount>();
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts WHERE company_id = @company_id AND is_delete = 0";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ReceivableAccount ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
result.Add(ra);
}
}
return result;
}
public List<ReceivableAccount> GetReceivableAccount()
{
List<ReceivableAccount> result = new List<ReceivableAccount>();
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
ReceivableAccount ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
result.Add(ra);
}
}
return result;
}
public static bool CreateReceivableAccount(ReceivableAccount ra)
{
if (dal.GetReceivableAccountByNumber(ra.AccountNumber) != null)
{
return false;
}
dal.CreateReceivableAccount(ra);
return true;
}
public void CreateReceivableAccount(ReceivableAccount ra)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@account_number", 50, ra.AccountNumber),
SqlUtilities.GenerateInputNVarcharParameter("@account_name", 50, ra.AccountName),
SqlUtilities.GenerateInputNVarcharParameter("@bank_name", 50, ra.BankName),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 1000, ra.Remark)
};
string sql = "INSERT INTO receivable_accounts(account_number, account_name, bank_name, remark) VALUES(@account_number, @account_name, @bank_name, @remark)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
protected void Page_Load(object sender, EventArgs e)
{
int id = 0;
if (int.TryParse(Request.QueryString["id"], out id))
{
ra = ReceivableAccountOperation.GetReceivableAccountById(id);
}
if (!IsPostBack)
{
FormDataBind();
}
}
protected void btnCreate_Click(object sender, EventArgs e)
{
string accounName = Request.Form[txtAccountName.ID].Trim();
string accountNumber = Request.Form[txtAccountNumber.ID].Trim();
string bankName = Request.Form[txtBankName.ID].Trim();
string remark = Request.Form[txtRemark.ID].Trim();
if (string.IsNullOrEmpty(accounName) || Validator.IsMatchLessThanChineseCharacter(accounName, CONST_ACCOUNT_NAME_LENGTH))
{
lblMsg.Text = "账户名不能为空，并且长度不能超过 " + CONST_ACCOUNT_NAME_LENGTH + " 个字符！";
return;
}
if (string.IsNullOrEmpty(accountNumber) || Validator.IsMatchLessThanChineseCharacter(accountNumber, CONST_ACCOUNT_NUMBER_LENGTH))
{
lblMsg.Text = "账号不能为空，并且长度不能超过 " + CONST_ACCOUNT_NUMBER_LENGTH + " 个字符！";
return;
}
if (string.IsNullOrEmpty(bankName) || Validator.IsMatchLessThanChineseCharacter(bankName, CONST_BANK_NAME_LENGTH))
{
lblMsg.Text = "开户银行名称不能为空，并且长度不能超过 " + CONST_BANK_NAME_LENGTH + " 个字符！";
return;
}
if (!string.IsNullOrEmpty(remark) && Validator.IsMatchLessThanChineseCharacter(remark, CONST_REMARK_LENGTH))
{
lblMsg.Text = "备注长度不能超过 " + CONST_REMARK_LENGTH + " 个字符！";
return;
}
ReceivableAccount ra = new ReceivableAccount();
ra.AccountName = accounName;
ra.AccountNumber = accountNumber;
ra.BankName = bankName;
ra.Remark = remark;
ra.CompanyId = user.CompanyId;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = int.Parse(ddlPaymentMethod.SelectedItem.Value);
if (ReceivableAccountOperation.CreateReceivableAccount(ra))
{
lblMsg.Text = "添加成功！";
}
else
{
lblMsg.Text = "该账号已经存在！";
return;
}
}
public List<ReceivableAccount> GetReceivableAccount()
{
List<ReceivableAccount> result = new List<ReceivableAccount>();
string sql = "SELECT id, account_number, account_name, bank_name, remark FROM receivable_accounts";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
ReceivableAccount ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.AccountNumber = dr.GetString(1);
ra.AccountName = dr.GetString(2);
ra.BankName = dr.GetString(3);
ra.Remark = dr.GetString(4);
result.Add(ra);
}
}
return result;
}
public ReceivableAccount GetReceivableAccountById(int id)
{
ReceivableAccount ra = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, account_number, account_name, bank_name, remark FROM receivable_accounts WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.AccountNumber = dr.GetString(1);
ra.AccountName = dr.GetString(2);
ra.BankName = dr.GetString(3);
ra.Remark = dr.GetString(4);
}
}
return ra;
}
public void UpdateReceivableAccount(ReceivableAccount ra)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", ra.Id),
SqlUtilities.GenerateInputNVarcharParameter("@account_number", 50, ra.AccountNumber),
SqlUtilities.GenerateInputNVarcharParameter("@account_name", 50, ra.AccountName),
SqlUtilities.GenerateInputNVarcharParameter("@bank_name", 50, ra.BankName),
SqlUtilities.GenerateInputNVarcharParameter("@remark", 1000, ra.Remark)
};
string sql = "UPDATE receivable_accounts SET account_number = @account_number, account_name = @account_name, bank_name = @bank_name, remark = @remark WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static void UpdateReceivableAccount(ReceivableAccount ra)
{
dal.UpdateReceivableAccount(ra);
}
public ReceivableAccount GetReceivableAccountByNumber(string number)
{
ReceivableAccount ra = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@account_number", 50, number)
};
string sql = "SELECT id, company_id, payment_method_id, account_number, account_name, bank_name, remark FROM receivable_accounts WHERE account_number = @account_number";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ra = new ReceivableAccount();
ra.Id = dr.GetInt32(0);
ra.CompanyId = dr.GetInt32(1);
Company comp = new CompanyDAL().GetCompanyById(ra.CompanyId);
ra.CompanyName = comp.Name;
ra.PaymentMethod = new PaymentMethod();
ra.PaymentMethod.Id = dr.GetInt32(2);
PaymentMethod pm = new PaymentMethodDAL().GetPaymentMethodById(ra.PaymentMethod.Id);
ra.PaymentMethod.Name = pm.Name;
ra.AccountNumber = dr.GetString(3);
ra.AccountName = dr.GetString(4);
ra.BankName = dr.GetString(5);
ra.Remark = dr.GetString(6);
}
}
return ra;
}
