private bool CheckLendable(LendArchive ai)
{
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 ReturnArchive where archId={0} and id <> 0),0) - ifnull((select sum(copies) from lendArchive where archId={0} and id <> {4}),0) - {1}) as copies from archiveInfo where archiveName='{2}' And ProjectId={3}", ai.ArchId, ai.Copies, ai.ArchiveName, CurrentArchiveInfo.ProjectId, ai.Id);
//sql_cmd.CommandText = string.Format("select remaining - {1} as copies from archiveInfo where id='{0}' And ProjectId={2}", ai.ArchId, ai.Copies,CurrentArchiveInfo.ProjectId);
int value = Convert.ToInt32(sql_cmd.ExecuteScalar());
conn.Close();
return(value >= 0);
}
}
private string NotifyLendArchiveRemaining(LendArchive 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(string.Format("update archiveInfo set remaining = {1} where id = {0}", ai.ArchId, remaining));
}
private string GenLendArchiveSQL(LendArchive ai)
{
if (ai.Id == 0)
{
return("insert into LendArchive(archiveName, LendDate, Copies, LendReason, LendUnit, Handler, Phone, ExpectedReturnDate,Borrower, ApprovedBy, NeedReturn, ArchId)"
+ " values ('" + ai.ArchiveName + "','" + ai.LendDate + "'," + ai.Copies + ",'" + ai.LendReason + "','"
+ ai.LendUnit + "','" + ai.Handler + "','" + ai.Phone + "','" + ai.ExpectedReturnDate + "','" + ai.Borrower + "','"
+ ai.ApprovedBy + "','" + ai.NeedReturn + "'," + ai.ArchId + ")");
}
return("update LendArchive set archiveName='" + ai.ArchiveName + "', LendDate='" + ai.LendDate
+ "', Copies=" + ai.Copies + ", LendReason='" + ai.LendReason + "', LendUnit='" + ai.LendUnit
+ "', Handler='" + ai.Handler + "', Phone='" + ai.Phone + "', ExpectedReturnDate='" + ai.ExpectedReturnDate
+ "', Borrower='" + ai.Borrower + "', ApprovedBy='" + ai.ApprovedBy + "', NeedReturn='" + ai.NeedReturn + "' where id =" + ai.Id);
}
private void LendArchiveRetreeIdFixRowCellReadonly(GridRow gr, LendArchive 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='LendArchive'";
ai.Id = Convert.ToInt32(sql_cmd.ExecuteScalar());
gr["gcId"].Value = ai.Id;
conn.Close();
}
}
}
private int SaveLendArchiveInfo(List <GridRow> list)
{
int changeCount = 0;
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
try
{
foreach (GridRow gr in list)
{
LendArchive ai = GridCellMapToLendArchive(gr);
if (!CheckLendable(ai))
{
gr["gcCount"].CellStyles.Default.TextColor = Color.Red;
ToastMessage.Show(this, ai.ArchiveName + " 剩余数量不足，请重新修改借出数量");
continue;
}
cmd.CommandText = GenLendArchiveSQL(ai);
cmd.ExecuteNonQuery();
gr["gcCount"].CellStyles.Default.TextColor = Color.Black;
cmd.CommandText = NotifyLendArchiveRemaining(ai);
cmd.ExecuteNonQuery();
LendArchiveRetreeIdFixRowCellReadonly(gr, ai);
changeCount++;
}
}
catch (System.Data.SQLite.SQLiteException E)
{
MessageBox.Show(cmd.CommandText + Environment.NewLine + E.Message);
}
finally
{
conn.Close();
}
}
return(changeCount);
}