/// <summary>
/// 这是查询分类船舶的方法
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public List <BillIndex> GetReocrdBySearch(string where)
{
string sql = "select b.billIndexID from bill b left join company p on p.id=b.company where b.company in (" + where + ") group by b.billIndexID";
List <BillIndex> bi = new List <BillIndex>();
using (SQLiteDataReader sdr = SQLiteHelper.ExecuteReader(CommandType.Text, sql))
{
if (sdr == null)
{
return(bi);
}
while (sdr.Read())
{
BillIndex b = new BillIndex();
b.ID = sdr["billindexID"].ToString();
b = new BillIndexService().GetRecord(" id=" + b.ID);
bi.Add(b);
}
}
return(bi);
}
/// <summary>
/// 这是检查基础数据的方法
/// </summary>
/// <returns></returns>
public List <BillIndex> GetReocrdByCheck()
{
string sql = "select b.billIndexID from bill b left join proxy p on p.id=b.proxy where b.proxy>0 and (typeof(p.id)='null' or p.id='') group by b.billIndexID";
List <BillIndex> bi = new List <BillIndex>();
using (SQLiteDataReader sdr = SQLiteHelper.ExecuteReader(CommandType.Text, sql))
{
if (sdr == null)
{
return(bi);
}
while (sdr.Read())
{
BillIndex b = new BillIndex();
b.ID = sdr["billindexID"].ToString();
b = new BillIndexService().GetRecord(" id=" + b.ID);
bi.Add(b);
}
}
return(bi);
}
/// <summary>
/// 更新票据明细
/// </summary>
/// <param name="bs"></param>
/// <param name="IndexID"></param>
/// <returns></returns>
public string UpdateBill(List <Bill> bs, string IndexID)
{
BillIndex bi = new BillIndexService().GetRecord(" id=" + IndexID); //获取票据索引记录
string delete = "delete from bill where billindexid=" + bi.ID;
if (string.IsNullOrEmpty(bi.ID))
{
return("没有找到票据ID=" + IndexID + "号的记录");
}
SQLiteCommand cmd = new SQLiteCommand();
SQLiteConnection conn = SQLiteHelper.SqlConn;
SQLiteTransaction trans = null;
try
{
cmd.Connection = conn;
trans = conn.BeginTransaction(); //开始
cmd.CommandType = CommandType.Text;
cmd.CommandText = delete;
cmd.ExecuteNonQuery(); //头一步删除旧记录
foreach (Bill b in bs)
{
string insertBill = InsertSQL(b, bi.Title, bi.ID);
cmd.CommandText = insertBill;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(ex.Message);
}
return("");
}