/// <summary>
/// get all accpayablelist
/// <summary>
/// <param name=accname>accname</param>
/// <param name=vendorname>vendorname</param>
/// <param name=out emsg>return error message</param>
///<returns>details of all accreceivablelist</returns>
public BindingCollection <modAccPayableList> GetIList(string accname, string vendorname, out string emsg)
{
try
{
BindingCollection <modAccPayableList> modellist = new BindingCollection <modAccPayableList>();
//Execute a query to read the categories
string sql = string.Format("select a.id,a.acc_name,a.seq,a.acc_date,a.vendor_name,a.currency,a.exchange_rate,a.start_mny,a.adding_mny,a.paid_mny,a.form_id,a.form_type,a.remark,a.update_user,a.update_time from acc_payable_list a where a.acc_name='{0}' and a.vendor_name='{1}' order by a.id", accname, vendorname);
decimal totalstart = 0;
decimal totaladding = 0;
decimal totalpaid = 0;
modAccPayableList model;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
while (rdr.Read())
{
model = new modAccPayableList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.AccName = dalUtility.ConvertToString(rdr["acc_name"]);
model.Seq = dalUtility.ConvertToInt(rdr["seq"]);
model.AccDate = dalUtility.ConvertToDateTime(rdr["acc_date"]);
model.VendorName = dalUtility.ConvertToString(rdr["vendor_name"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.StartMny = dalUtility.ConvertToDecimal(rdr["start_mny"]);
model.AddingMny = dalUtility.ConvertToDecimal(rdr["adding_mny"]);
model.PaidMny = dalUtility.ConvertToDecimal(rdr["paid_mny"]);
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.FormType = dalUtility.ConvertToString(rdr["form_type"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
model.UpdateUser = dalUtility.ConvertToString(rdr["update_user"]);
model.UpdateTime = dalUtility.ConvertToDateTime(rdr["update_time"]);
modellist.Add(model);
totalstart += model.StartMny * model.ExchangeRate;
totaladding += model.AddingMny * model.ExchangeRate;
totalpaid += model.PaidMny * model.ExchangeRate;
}
}
model = new modAccPayableList();
model.AccName = accname;
model.VendorName = "合计";
model.Currency = "人民币";
model.ExchangeRate = 1;
model.StartMny = totalstart;
model.AddingMny = totaladding;
model.PaidMny = totalpaid;
model.EndMny = totalstart + totaladding - totalpaid;
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 accpayablelist</returns>
public modAccPayableList GetItem(int?id, out string emsg)
{
try
{
//Execute a query to read the categories
string sql = string.Format("select id,acc_name,seq,acc_date,vendor_name,currency,exchange_rate,start_mny,adding_mny,paid_mny,form_id,form_type,remark,update_user,update_time from acc_payable_list where ID={0} order by id", id);
using (SqlDataReader rdr = SqlHelper.ExecuteReader(sql))
{
if (rdr.Read())
{
modAccPayableList model = new modAccPayableList();
model.Id = dalUtility.ConvertToInt(rdr["id"]);
model.AccName = dalUtility.ConvertToString(rdr["acc_name"]);
model.Seq = dalUtility.ConvertToInt(rdr["seq"]);
model.AccDate = dalUtility.ConvertToDateTime(rdr["acc_date"]);
model.VendorName = dalUtility.ConvertToString(rdr["vendor_name"]);
model.Currency = dalUtility.ConvertToString(rdr["currency"]);
model.ExchangeRate = dalUtility.ConvertToDecimal(rdr["exchange_rate"]);
model.StartMny = dalUtility.ConvertToDecimal(rdr["start_mny"]);
model.AddingMny = dalUtility.ConvertToDecimal(rdr["adding_mny"]);
model.PaidMny = dalUtility.ConvertToDecimal(rdr["paid_mny"]);
model.FormId = dalUtility.ConvertToString(rdr["form_id"]);
model.FormType = dalUtility.ConvertToString(rdr["form_type"]);
model.Remark = dalUtility.ConvertToString(rdr["remark"]);
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>
/// update a accpayablelist
/// <summary>
/// <param name=id>id</param>
/// <param name=mod>model object of accpayablelist</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Update(int?id, modAccPayableList mod, out string emsg)
{
try
{
string sql = string.Format("update acc_payable_list set acc_name='{0}',seq={1},acc_date='{2}',vendor_name='{3}',currency='{4}',exchange_rate={5},start_mny={6},adding_mny={7},paid_mny={8},form_id='{9}',form_type='{10}',remark='{11}',update_user='******',update_time=getdate() where id={13}", mod.AccName, mod.Seq, mod.AccDate, mod.VendorName, mod.Currency, mod.ExchangeRate, mod.StartMny, mod.AddingMny, mod.PaidMny, mod.FormId, mod.FormType, 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 accpayablelist
/// <summary>
/// <param name=mod>model object of accpayablelist</param>
/// <param name=out emsg>return error message</param>
/// <returns>true/false</returns>
public bool Insert(modAccPayableList mod, out string emsg)
{
try
{
string sql = string.Format("insert into acc_payable_list(acc_name,seq,acc_date,vendor_name,currency,exchange_rate,start_mny,adding_mny,paid_mny,form_id,form_type,remark,update_user,update_time)values('{0}',{1},'{2}','{3}','{4}',{5},{6},{7},{8},'{9}','{10}','{11}','{12}',getdate())", mod.AccName, mod.Seq, mod.AccDate, mod.VendorName, mod.Currency, mod.ExchangeRate, mod.StartMny, mod.AddingMny, mod.PaidMny, mod.FormId, mod.FormType, mod.Remark, 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);
}
}
