/// <summary>
/// get table record
/// <summary>
/// <param name=accountno>accountno</param>
/// <param name=out emsg>return error message</param>
///<returns>get a record detail of accbankaccount</returns>
public modAccBankAccount GetItem(string accountno, out string emsg)
{
try
{
//Execute a query to read the categories
string sql = string.Format("select a.account_no,a.bank_name,a.currency,b.exchange_rate,a.tax_flag,a.update_user,a.update_time from acc_bank_account a inner join acc_currency_list b on a.currency=b.currency order where account_no='{0}' by a.account_no", accountno);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modAccBankAccount model = new modAccBankAccount();
model.AccountNo = dalUtility.ConvertToString(rdr["account_no"]);
model.BankName = dalUtility.ConvertToString(rdr["bank_name"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.TaxFlag = dalUtility.ConvertToInt(rdr["tax_flag"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
emsg = null;
return(model);
}
else
{
emsg = "Error on read data";
return(null);
}
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
/// <summary>
/// get all accbankaccount
/// <summary>
/// <param name=out emsg>return error message</param>
///<returns>details of all accbankaccount</returns>
public BindingCollection <modAccBankAccount> GetIList(out string emsg)
{
try
{
BindingCollection <modAccBankAccount> modellist = new BindingCollection <modAccBankAccount>();
//Execute a query to read the categories
string sql = @"select a.account_no,a.bank_name,a.currency,b.exchange_rate,a.tax_flag,a.update_user,a.update_time
from acc_bank_account a inner join acc_currency_list b on a.currency=b.currency order by a.account_no" ;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modAccBankAccount model = new modAccBankAccount();
model.AccountNo = dalUtility.ConvertToString(rdr["account_no"]);
model.BankName = dalUtility.ConvertToString(rdr["bank_name"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.TaxFlag = dalUtility.ConvertToInt(rdr["tax_flag"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
modellist.Add(model);
}
}
emsg = null;
return(modellist);
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(null);
}
}
private void DBGrid_SelectionChanged(object sender, EventArgs e)
{
if (DBGrid.CurrentRow != null)
{
modAccBankAccount mod = (modAccBankAccount)DBGrid.CurrentRow.DataBoundItem;
txtAccountNo.Text = mod.AccountNo;
txtBankName.Text = mod.BankName;
cboCurrency.SelectedValue = mod.Currency;
cboTaxFlag.SelectedIndex = mod.TaxFlag == 1 ? 0 : 1;
FindText = mod.AccountNo;
}
}
protected override void Find()
{
for (int i = 0; i < DBGrid.Rows.Count; i++)
{
modAccBankAccount mod = (modAccBankAccount)DBGrid.Rows[i].DataBoundItem;
if (mod.AccountNo.CompareTo(FindText) == 0)
{
DBGrid.CurrentCell = DBGrid.Rows[i].Cells[0];
DBGrid_SelectionChanged(null, null);
return;
}
}
}
public static decimal GetExchangeRate(string accountno)
{
dalAccBankAccount dal = new dalAccBankAccount();
modAccBankAccount mod = dal.GetItem(accountno, out Util.emsg);
if (mod != null)
{
dalAccCurrencyList dal2 = new dalAccCurrencyList();
modAccCurrencyList mod2 = dal2.GetItem(mod.Currency, out Util.emsg);
if (mod2 != null)
{
return(mod2.ExchangeRate);
}
else
{
return(1);
}
}
else
{
return(1);
}
}
/// <summary>
/// insert a accbankaccount
/// <summary>
/// <param name=mod>model object of accbankaccount</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Insert(modAccBankAccount mod, out string emsg)
{
try
{
string sql = string.Format("insert into acc_bank_account(account_no,bank_name,currency,tax_flag,update_user,update_time)values('{0}','{1}','{2}',{3},'{4}',getdate())", mod.AccountNo, mod.BankName, mod.Currency, mod.TaxFlag, mod.UpdateUser);
int i = SqlHelper.ExecuteNonQuery(sql);
if (i > 0)
{
emsg = null;
return(true);
}
else
{
emsg = "Unknown error when ExecuteNonQuery!";
return(false);
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
}
/// <summary>
/// update a accbankaccount
/// <summary>
/// <param name=accountno>accountno</param>
/// <param name=mod>model object of accbankaccount</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Update(string accountno, modAccBankAccount mod, out string emsg)
{
try
{
string sql = string.Format("update acc_bank_account set bank_name='{0}',currency='{1}',tax_flag={2},update_user='******',update_time=getdate() where account_no='{4}'", mod.BankName, mod.Currency, mod.TaxFlag, mod.UpdateUser, accountno);
int i = SqlHelper.ExecuteNonQuery(sql);
if (i > 0)
{
emsg = null;
return(true);
}
else
{
emsg = "Unknown error when ExecuteNonQuery!";
return(false);
}
}
catch (Exception ex)
{
emsg = dalUtility.ErrorMessage(ex.Message);
return(false);
}
}
protected override bool Save()
{
try
{
this.Cursor = Cursors.WaitCursor;
if (string.IsNullOrEmpty(txtAccountNo.Text.Trim()))
{
MessageBox.Show(clsTranslate.TranslateString("Account No") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtAccountNo.Focus();
return(false);
}
if (string.IsNullOrEmpty(txtBankName.Text.Trim()))
{
MessageBox.Show(clsTranslate.TranslateString("Bank Name") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtBankName.Focus();
return(false);
}
if (cboCurrency.SelectedIndex == -1)
{
MessageBox.Show(clsTranslate.TranslateString("Currency") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
cboCurrency.Focus();
return(false);
}
if (cboTaxFlag.SelectedIndex == -1)
{
MessageBox.Show(clsTranslate.TranslateString("Tax Flag") + clsTranslate.TranslateString(" can not be null!"), clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
cboTaxFlag.Focus();
return(false);
}
modAccBankAccount mod = new modAccBankAccount();
mod.AccountNo = txtAccountNo.Text.Trim();
mod.BankName = txtBankName.Text.Trim();
mod.Currency = cboCurrency.SelectedValue.ToString();
mod.TaxFlag = cboTaxFlag.SelectedIndex == 0 ? 1 : 0;
mod.UpdateUser = Util.UserId;
bool ret = false;
if (_status == 1)
{
ret = _dal.Insert(mod, out Util.emsg);
}
else if (_status == 2)
{
ret = _dal.Update(txtAccountNo.Text, mod, out Util.emsg);
}
if (ret)
{
Util.ChangeStatus(this, true);
DBGrid.Enabled = true;
LoadData();
FindText = mod.AccountNo;
Find();
}
return(ret);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
finally
{
this.Cursor = Cursors.Default;
}
}
private void LoadData()
{
if (prepared == false)
{
return;
}
if (DBGrid.CurrentRow == null)
{
return;
}
switch (cboBookType.SelectedIndex)
{
case 0: //应收
modCustomerSimpleList modCust = (modCustomerSimpleList)DBGrid.CurrentRow.DataBoundItem;
dalAccReceivableList dalRec = new dalAccReceivableList();
BindingCollection <modReceivableBook> listRec = dalRec.GetReceivableBook(modCust.CustId, dtpFrom.Value, dtpTo.Value, out Util.emsg);
DBGrid2.DataSource = listRec;
break;
case 1: //应付
modVendorList modVendor = (modVendorList)DBGrid.CurrentRow.DataBoundItem;
dalAccPayableList dalPay = new dalAccPayableList();
BindingCollection <modPayableBook> listPay = dalPay.GetPayableBook(modVendor.VendorName, dtpFrom.Value, dtpTo.Value, out Util.emsg);
DBGrid2.DataSource = listPay;
break;
case 2: //其它应收
modOtherReceivableObject modObjRec = (modOtherReceivableObject)DBGrid.CurrentRow.DataBoundItem;
dalAccOtherReceivable dalORec = new dalAccOtherReceivable();
BindingCollection <modOtherReceivableBook> listORec = dalORec.GetOtherReceivableBook(modObjRec.ObjectName, dtpFrom.Value, dtpTo.Value, out Util.emsg);
DBGrid2.DataSource = listORec;
break;
case 3: //其它应付
modOtherPayableObject modObjPay = (modOtherPayableObject)DBGrid.CurrentRow.DataBoundItem;
dalAccOtherPayable dalOPay = new dalAccOtherPayable();
BindingCollection <modOtherPayableBook> listOPay = dalOPay.GetOtherPayableBook(modObjPay.ObjectName, dtpFrom.Value, dtpTo.Value, out Util.emsg);
DBGrid2.DataSource = listOPay;
break;
case 4: //现金银行
modAccBankAccount modCash = (modAccBankAccount)DBGrid.CurrentRow.DataBoundItem;
dalAccReport dalCash = new dalAccReport();
BindingCollection <modAccCredenceBook> listCash = dalCash.GetCashAndBankBook(modCash.AccountNo, dtpFrom.Value, dtpTo.Value, out Util.emsg);
DBGrid2.DataSource = listCash;
break;
}
for (int i = 0; i < DBGrid2.RowCount; i++)
{
if (DBGrid2.Rows[i].Cells["AccSeq"].Value.ToString() == "本月合计")
{
DBGrid2.Rows[i].DefaultCellStyle.ForeColor = Color.Red;
}
}
if (DBGrid2.RowCount > 0)
{
DBGrid2.Columns[0].Visible = false;
DBGrid2.Columns["StartMny"].Width = 120;
DBGrid2.Columns["AddingMny"].Width = 120;
DBGrid2.Columns["PaidMny"].Width = 120;
DBGrid2.Columns["EndMny"].Width = 120;
DBGrid2.Columns["StartMny"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
DBGrid2.Columns["AddingMny"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
DBGrid2.Columns["PaidMny"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
DBGrid2.Columns["EndMny"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
}
