/// <summary>
/// get wait credence list
/// <summary>
/// <param name=subjectlist>subjectlist</param>
/// <param name=out emsg>return error message</param>
///<returns>accchecklist</returns>
public BindingCollection <modAccCheckList> GetWaitingCredenceList(string subjectlist, out string emsg)
{
try
{
BindingCollection <modAccCheckList> modellist = new BindingCollection <modAccCheckList>();
//Execute a query to read the categories
string subjectwhere = string.Empty;
if (!string.IsNullOrEmpty(subjectlist) && subjectlist.CompareTo("ALL") != 0)
{
subjectwhere = "and a.subject_id in ('" + subjectlist.Replace(",", "','") + "') ";
}
string sql = "select a.id,a.acc_name,a.acc_seq,a.check_no,a.subject_id,b.subject_name,a.bank_name,a.check_type,a.account_no,a.form_type,a.form_id,a.remark,a.currency,mny,a.exchange_rate,a.create_date,a.promise_date,a.get_date,a.status,a.update_user,a.update_time "
+ "from acc_check_list a inner join acc_subject_list b on a.subject_id=b.subject_id where not exists (select 1 from acc_check_form where check_id=a.id) " + subjectlist + "order by a.id";
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modAccCheckList model = new modAccCheckList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.AccName = dalUtility.ConvertToString(rdr["acc_name"]);
model.AccSeq = dalUtility.ConvertToInt(rdr["acc_seq"]);
model.CheckNo = dalUtility.ConvertToString(rdr["check_no"]);
model.SubjectId = dalUtility.ConvertToString(rdr["subject_id"]);
model.SubjectName = dalUtility.ConvertToString(rdr["subject_name"]);
model.BankName = dalUtility.ConvertToString(rdr["bank_name"]);
model.CheckType = dalUtility.ConvertToString(rdr["check_type"]);
model.AccountNo = dalUtility.ConvertToString(rdr["account_no"]);
model.FormType = dalUtility.ConvertToString(rdr["form_type"]);
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.Mny = dalUtility.ConvertToDecimal(rdr["mny"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.CreateDate = dalUtility.ConvertToDateTime(rdr["create_date"]);
model.PromiseDate = dalUtility.ConvertToDateTime(rdr["promise_date"]);
model.GetDate = dalUtility.ConvertToDateTime(rdr["get_date"]);
model.Status = dalUtility.ConvertToInt(rdr["status"]);
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);
}
}
/// <summary>
/// get table record
/// <summary>
/// <param name=id>id</param>
/// <param name=out emsg>return error message</param>
///<returns>get a record detail of accchecklist</returns>
public modAccCheckList GetItem(int?id, out string emsg)
{
try
{
//Execute a query to read the categories
string sql = string.Format("select a.id,a.acc_name,a.acc_seq,a.check_no,a.subject_id,b.subject_name,a.bank_name,a.check_type,a.account_no,a.form_type,a.form_id,a.remark,a.currency,mny,a.exchange_rate,a.create_date,a.promise_date,a.get_date,a.status,a.update_user,a.update_time "
+ "from acc_check_list a inner join acc_subject_list b on a.subject_id=b.subject_id where ID={0} order by a.id", id);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modAccCheckList model = new modAccCheckList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.AccName = dalUtility.ConvertToString(rdr["acc_name"]);
model.AccSeq = dalUtility.ConvertToInt(rdr["acc_seq"]);
model.CheckNo = dalUtility.ConvertToString(rdr["check_no"]);
model.SubjectId = dalUtility.ConvertToString(rdr["subject_id"]);
model.SubjectName = dalUtility.ConvertToString(rdr["subject_name"]);
model.BankName = dalUtility.ConvertToString(rdr["bank_name"]);
model.CheckType = dalUtility.ConvertToString(rdr["check_type"]);
model.AccountNo = dalUtility.ConvertToString(rdr["account_no"]);
model.FormType = dalUtility.ConvertToString(rdr["form_type"]);
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.Mny = dalUtility.ConvertToDecimal(rdr["mny"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.CreateDate = dalUtility.ConvertToDateTime(rdr["create_date"]);
model.PromiseDate = dalUtility.ConvertToDateTime(rdr["promise_date"]);
model.GetDate = dalUtility.ConvertToDateTime(rdr["get_date"]);
model.Status = dalUtility.ConvertToInt(rdr["status"]);
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 accchecklist
/// <summary>
/// <param name=accname>accname</param>
/// <param name=subjectid>subjectid</param>
/// <param name=currency>currency</param>
/// <param name=exchangerate>exchangerate</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all accchecklist</returns>
public BindingCollection <modAccCheckList> GetStartCheckList(string accname, string subjectid, string currency, decimal exchangerate, out string emsg)
{
try
{
BindingCollection <modAccCheckList> modellist = new BindingCollection <modAccCheckList>();
//Execute a query to read the categories
string sql = string.Format("select a.id,a.acc_name,a.acc_seq,a.check_no,a.subject_id,b.subject_name,a.bank_name,a.check_type,a.account_no,a.form_type,a.form_id,a.remark,a.currency,mny,a.exchange_rate,a.create_date,a.promise_date,a.get_date,a.status,a.update_user,a.update_time "
+ "from acc_check_list a inner join acc_subject_list b on a.subject_id=b.subject_id where a.acc_name='{0}' and a.acc_seq = 0 and a.currency='{1}' and a.subject_id='{2}' order by a.id", accname, currency, subjectid);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
modAccCheckList model = new modAccCheckList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.AccName = dalUtility.ConvertToString(rdr["acc_name"]);
model.AccSeq = dalUtility.ConvertToInt(rdr["acc_seq"]);
model.CheckNo = dalUtility.ConvertToString(rdr["check_no"]);
model.SubjectId = dalUtility.ConvertToString(rdr["subject_id"]);
model.SubjectName = dalUtility.ConvertToString(rdr["subject_name"]);
model.BankName = dalUtility.ConvertToString(rdr["bank_name"]);
model.CheckType = dalUtility.ConvertToString(rdr["check_type"]);
model.AccountNo = dalUtility.ConvertToString(rdr["account_no"]);
model.FormType = dalUtility.ConvertToString(rdr["form_type"]);
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.Mny = dalUtility.ConvertToDecimal(rdr["mny"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.CreateDate = dalUtility.ConvertToDateTime(rdr["create_date"]);
model.PromiseDate = dalUtility.ConvertToDateTime(rdr["promise_date"]);
model.GetDate = dalUtility.ConvertToDateTime(rdr["get_date"]);
model.Status = dalUtility.ConvertToInt(rdr["status"]);
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 mnuAccCredenceList_Click(object sender, EventArgs e)
{
try
{
this.Cursor = Cursors.WaitCursor;
if (DBGrid.CurrentRow == null)
{
return;
}
modAccCheckList mod = (modAccCheckList)DBGrid.CurrentRow.DataBoundItem;
if (mod.Status == 0)
{
EditAccCredenceList frm = new EditAccCredenceList();
frm.EditItem(mod.AccName, mod.AccSeq);
if (frm.ShowDialog() == DialogResult.OK)
{
LoadData();
}
}
else
{
EditAccCheckForm frm = new EditAccCheckForm();
dalAccCheckForm dalcf = new dalAccCheckForm();
modAccCheckForm modcf = dalcf.GetItembyCheckid(mod.Id, out Util.emsg);
if (modcf != null)
{
frm.EditItem(modcf.FormId);
frm.ShowDialog();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, clsTranslate.TranslateString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
finally
{
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// update a accchecklist
/// <summary>
/// <param name=id>id</param>
/// <param name=mod>model object of accchecklist</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Update(int?id, modAccCheckList mod, out string emsg)
{
try
{
string sql = string.Format("update acc_check_list set acc_name='{0}',acc_seq={1},check_no='{2}',subject_id='{3}',bank_name='{4}',check_type='{5}',account_no='{6}',form_type='{7}',form_id='{8}',currency='{9}',mny={10},exchange_rate={11},create_date='{12}',promise_date='{13}',get_date='{14}',status={15},update_user='******',update_time=getdate(),remark='{17}' where id={18}", mod.AccName, mod.AccSeq, mod.CheckNo, mod.SubjectId, mod.BankName, mod.CheckType, mod.AccountNo, mod.FormType, mod.FormId, mod.Currency, mod.Mny, mod.ExchangeRate, mod.CreateDate, mod.PromiseDate, mod.GetDate, mod.Status, mod.Remark, mod.UpdateUser, id);
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>
/// insert a accchecklist
/// <summary>
/// <param name=mod>model object of accchecklist</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Insert(modAccCheckList mod, out string emsg)
{
try
{
string sql = string.Format("insert into acc_check_list(acc_name,acc_seq,check_no,subject_id,bank_name,check_type,account_no,form_type,form_id,currency,mny,exchange_rate,create_date,promise_date,status,update_user,update_time,remark)values('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},{11},'{12}','{13}',{14},'{15}',getdate(),'{16}')", mod.AccName, mod.AccSeq, mod.CheckNo, mod.SubjectId, mod.BankName, mod.CheckType, mod.AccountNo, mod.FormType, mod.FormId, mod.Currency, mod.Mny, mod.ExchangeRate, mod.CreateDate, mod.PromiseDate, 0, mod.UpdateUser, mod.Remark);
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);
}
}
private void btnCheckId_Click(object sender, EventArgs e)
{
dalAccCheckList dal = new dalAccCheckList();
BindingCollection <modAccCheckList> list = dal.GetWaitingCredenceList(string.Empty, out Util.emsg);
if (list != null)
{
frmViewList frm = new frmViewList();
frm.InitViewList("请选择要承兑的支票:", list);
frm.Selection = true;
if (frm.ShowDialog() == DialogResult.OK)
{
modAccCheckList mod = dal.GetItem(Convert.ToInt32(Util.retValue1), out Util.emsg);
txtCheckId.Text = mod.Id.ToString();
txtSubjectName.Text = mod.SubjectName;
txtCheckNo.Text = mod.CheckNo;
txtCheckType.Text = mod.CheckType;
txtBankName.Text = mod.BankName;
txtCurrency.Text = mod.Currency;
txtMny.Text = mod.Mny.ToString();
txtExchangeRate.Text = mod.ExchangeRate.ToString();
}
}
}
