public LendForm(ArchiveInfo ai)
{
InitializeComponent();
this.CurrentArchiveInfo = ai;
btnDelete.Visible = Authority.AllowDelete;
LendGrid.MouseWheel += new System.Windows.Forms.MouseEventHandler(mouseWheel);
}
private string GenSQL(ArchiveInfo ai)
{
if (ai.Id == 0)
{
return("insert into ArchiveInfo(archiveName, ArchType, ArchDate, DispatchNum, Copies, Remaining, StorageLocation, Handler, ProjectId, RegisterDate, Remark) values ('"
+ ai.ArchiveName + "','" + ai.ArchType + "','" + ai.ArchDate + "','" + ai.DispatchNum + "'," + ai.Copies + "," + ai.Remaining
+ ",'" + ai.StorageLocation + "','" + ai.Handler + "', " + ai.ProjectId + ", '" + ai.RegisterDate + "','" + ai.Remark + "')");
}
return("update ArchiveInfo set archiveName='" + ai.ArchiveName + "', ArchType='" + ai.ArchType + "', ArchDate='" + ai.ArchDate +
"', DispatchNum='" + ai.DispatchNum + "', Copies=" + ai.Copies + ", Remaining=" + ai.Remaining + ", StorageLocation='" + ai.StorageLocation
+ "', Handler='" + ai.Handler + "', ProjectId=" + ai.ProjectId + ",RegisterDate='" + ai.RegisterDate + "', Remark='" + ai.Remark + "' where id =" + ai.Id);
}
private int CalcRemaining(int newCount, ArchiveInfo ai)
{
string sql = string.Format("SELECT {1} - ifnull((SELECT sum(copies) FROM lendArchive WHERE archid = {0}),0) "
+ " + ifnull(( SELECT sum( copies ) FROM ReturnArchive WHERE archid = {0}),0) as Remaining "
+ " FROM ArchiveInfo WHERE id = {0}", ai.Id, newCount);
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// SQLiteDataReader reader = cmd.ExecuteReader();
return(Convert.ToInt32(cmd.ExecuteScalar())); //reader.GetInt16(0);
}
}
private void ToReturnClick(object sender, EventArgs e)
{
GridButtonXEditControl ddc = sender as GridButtonXEditControl;
GridRow row = ddc.EditorCell.GridRow;
if (row.Cells["gcArchName"].Value == null)
{
MessageBox.Show("请先保存资料，然后再归还资料");
return;
}
ArchiveInfo ai = (ArchiveInfo)row.Cells["gcId"].Tag;
ReturnForm form = new ReturnForm(ai);
form.ShowDialog();
btnRefreshArchive_Click(sender, e);
NavigateTo(ai.ArchiveName);
}
private void ArchiveGrid_EndEdit(object sender, GridEditEventArgs e)
{
if (!e.GridCell.GridColumn.Name.Equals("gcAllCount"))
{
return;
}
object o = e.GridCell.GridRow.Cells["gcId"].Tag;
ArchiveInfo ai = o as ArchiveInfo;
if (ai == null || !ai.HasLend)
{
e.GridCell.GridRow.Cells["gcRemaining"].Value = e.GridCell.Value;
}
else
{
int newCount = Convert.ToInt32(e.GridCell.GridRow.Cells["gcAllCount"].Value);
e.GridCell.GridRow.Cells["gcRemaining"].Value = CalcRemaining(newCount, ai);
}
}
private void ArchiveInfoRetreeIdFixRowCellReadonly(GridRow gr, ArchiveInfo ai)
{
gr.RowDirty = false;
if (ai.Id == 0)
{
gr[6].ReadOnly = ai.HasLend;
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand sql_cmd = conn.CreateCommand();
sql_cmd.CommandText = "select seq from sqlite_sequence where name='ArchiveInfo'; ";
ai.Id = Convert.ToInt32(sql_cmd.ExecuteScalar());
gr["gcId"].Value = ai.Id;
ai.HasLend = false;
gr["gcId"].Tag = ai;
conn.Close();
}
}
}
private void UpdateCascadeArchiveName(ArchiveInfo ai)
{
if (ai.Id < 1)
{
return;
}
string sql = string.Format("update LendArchive set ArchiveName = '{1}' where ArchId = {0}", ai.Id, ai.ArchiveName);
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = string.Format("update ReturnArchive set ArchiveName = '{1}' where ArchId = {0}", ai.Id, ai.ArchiveName);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
private void GetArchivesList()
{
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand sql_cmd = conn.CreateCommand();
sql_cmd.CommandText = string.Format("select a.Id,a.ArchiveName,ArchType,ArchDate,DispatchNum,a.Copies,Remaining,StorageLocation,a.Handler,ProjectId, RegisterDate, Remark"
+ ", count(b.Id) bcount from ArchiveInfo a left join lendArchive b on a.ArchiveName = b.ArchiveName where ProjectId = {0} "
+ " group by a.Id,a.ArchiveName,ArchType,ArchDate,DispatchNum,a.Copies,Remaining,StorageLocation,a.Handler,ProjectId, RegisterDate, Remark "
+ " order by ArchDate desc ", Project.Id);
SQLiteDataReader reader = sql_cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ArchiveInfo ai = new ArchiveInfo();
ai.Id = reader.GetInt16(0);
ai.ArchiveName = reader.GetString(1);
ai.ArchType = reader.GetString(2);
if (!reader.IsDBNull(3))
{
ai.ArchDate = Convert.ToDateTime(reader.GetString(3));
}
ai.DispatchNum = reader.IsDBNull(4) ? "" : reader.GetString(4);
ai.Copies = reader.GetInt16(5);
ai.Remaining = reader.GetInt16(6);
ai.StorageLocation = reader.IsDBNull(7) ? "" : reader.GetString(7);
ai.Handler = reader.IsDBNull(8) ? "" : reader.GetString(8);
ai.ProjectId = reader.GetInt16(9);
ai.RegisterDate = reader.IsDBNull(10) ? DateTime.Now : Convert.ToDateTime(reader.GetString(10));
ai.Remark = reader.IsDBNull(11) ? "" : reader.GetString(11);
ai.HasLend = reader.GetInt16(12) > 0;
ai.Project = Project;
ArchiveInfoList.Add(ai);
}
}
reader.Close();
conn.Close();
}
}
private void SaveArchiveInfo(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)
{
ArchiveInfo ai = GridCellMapToArchiveInfo(gr);
string strsql = GenSQL(ai);
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
ArchiveInfoRetreeIdFixRowCellReadonly(gr, ai);
UpdateCascadeArchiveName(ai);
ArchiveInfoList.Add(ai);
}
}
catch (System.Data.SQLite.SQLiteException E)
{
string msg = "保存失败。原因：" + E.Message;
if (E.Message.Contains("UNIQUE constraint failed"))
{
msg = "保存失败。原因：资料名称重复。";
}
MessageBox.Show(msg);
}
finally
{
conn.Close();
}
}
}
private void ProjectGrid_RowHeaderDoubleClick(object sender, GridRowHeaderDoubleClickEventArgs e)
{
GridPanel panel = ProjectGrid.PrimaryGrid;
GridRow row = (GridRow)panel.Rows[e.GridRow.RowIndex];
row.Rows.Clear();
int id = (int)row.Cells["gcId"].Value;
using (SQLiteConnection conn = new SQLiteConnection(DataSourceManager.DataSource))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
try
{
string Id = row.Cells[0].Value.ToString();
string strsql = string.Format("select * from ArchiveInfo where ProjectId={0} order by ArchDate desc", id);
cmd.CommandText = strsql;
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
GridPanel subPanel = new GridPanel();
SetArchivePanelColumn(subPanel);
while (reader.Read())
{
ArchiveInfo ai = new ArchiveInfo();
ai.Id = reader.GetInt16(0);
ai.ArchiveName = reader.GetString(1);
ai.ArchType = reader.GetString(2);
if (!reader.IsDBNull(3))
{
ai.ArchDate = Convert.ToDateTime(reader.GetString(3));
}
ai.DispatchNum = reader.IsDBNull(4) ? "" : reader.GetString(4);
ai.Copies = reader.GetInt16(5);
ai.Remaining = reader.GetInt16(6);
ai.StorageLocation = reader.IsDBNull(7) ? "" : reader.GetString(7);
ai.Handler = reader.IsDBNull(8) ? "" : reader.GetString(8);
GridRow gr = new GridRow();
gr.Cells.Add(new GridCell(ai.ArchiveName));
gr.Cells.Add(new GridCell(ai.Id));
gr.Cells.Add(new GridCell(ai.ArchType));
if (ai.ArchDate != null)
{
gr.Cells.Add(new GridCell(ai.ArchDate.ToString()));//"yyyy-MM-dd"
}
gr.Cells.Add(new GridCell(ai.DispatchNum));
gr.Cells.Add(new GridCell(ai.Copies));
gr.Cells.Add(new GridCell(ai.Remaining));
gr.Cells.Add(new GridCell(ai.StorageLocation));
gr.Cells.Add(new GridCell(ai.Handler));
subPanel.Rows.Add(gr);
}
e.GridRow.Rows.Add(subPanel);
}
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
}
}