private void SaveReturnArchiveInfo(List <GridRow> list)
{
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
try
{
foreach (GridRow gr in list)
{
ReturnArchive ai = GridCellMapToReturnArchive(gr);
cmd.CommandText = GenReturnSQL(ai);
cmd.ExecuteNonQuery();
if (ai.Id == 0)
{
cmd.CommandText = NotifyArchiveRemaining(ai);
cmd.ExecuteNonQuery();
ReturnArchiveRetreeIdFixRowCellReadonly(gr, ai);
gr["gcReturnCount"].AllowEdit = false;
}
}
}
catch (System.Data.SQLite.SQLiteException E)
{
MessageBox.Show(cmd.CommandText + Environment.NewLine + E.Message);
}
finally
{
conn.Close();
}
}
}
private string GenReturnSQL(ReturnArchive ai)
{
if (ai.Id == 0)
{
return("insert into ReturnArchive(archiveName, ReturnDate, Copies, Handler, DamageOrLost, Remark, Returner, ArchId) values ('"
+ ai.ArchiveName + "','" + ai.ReturnDate + "'," + ai.Copies + ",'" + ai.Handler + "'," + ai.DamageOrLost
+ ",'" + ai.Remark + "','" + ai.Returner + "'," + CurrentArchiveInfo.Id + ")");
}
return("update ReturnArchive set archiveName='" + ai.ArchiveName + "', ReturnDate='" + ai.ReturnDate + "', Copies=" + ai.Copies +
", Handler='" + ai.Handler + "', DamageOrLost=" + ai.DamageOrLost + ", Remark='" + ai.Remark + "', Returner='" + ai.Returner +
"' where id =" + ai.Id);
}
private string NotifyArchiveRemaining(ReturnArchive ai)
{
int remaining = 0;
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand sql_cmd = conn.CreateCommand();
sql_cmd.CommandText = string.Format("select copies - ifnull((select sum(copies) from lendArchive where archId={0} and id <> 0),0) + ifnull((select sum(copies) from ReturnArchive where archId={0} and id <> 0),0) as c from archiveInfo where id = {0}", ai.ArchId);
remaining = Convert.ToInt32(sql_cmd.ExecuteScalar());
conn.Close();
}
return("update ArchiveInfo set Remaining = " + remaining + " where Id='" + CurrentArchiveInfo.Id + "'");
}
private void ReturnArchiveRetreeIdFixRowCellReadonly(GridRow gr, ReturnArchive ai)
{
gr.RowDirty = false;
if (ai.Id == 0)
{
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand sql_cmd = conn.CreateCommand();
sql_cmd.CommandText = "select seq from sqlite_sequence where name='ReturnArchive'; ";
ai.Id = Convert.ToInt32(sql_cmd.ExecuteScalar());
gr["gcId"].Value = ai.Id;
conn.Close();
}
}
}