public static List<TimerType> GetTimerList()
{
List<TimerType> list = new List<TimerType>();
try
{
string sql = "SELECT pmo_number, description FROM timer_types";
SQLiteCommand command = new SQLiteCommand(sql, LocalSqllite.m_sqlLiteConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TimerType data = new TimerType();
data.pmo_num = reader["pmo_number"].ToString();
data.desc = reader["description"].ToString();
list.Add(data);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return list;
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (listViewTimers.SelectedItems.Count == 0)
{
MessageBox.Show("You have not selected an item.");
return;
}
ListViewItem itm = listViewTimers.SelectedItems[0];
m_selected = (TimerType)itm.Tag;
DialogResult = System.Windows.Forms.DialogResult.OK;
}
private void buttonStartTimer_Click(object sender, EventArgs e)
{
UpdateDatabaseTimeRecord(true);
TimeSelection dlg = new TimeSelection();
if (dlg.ShowDialog() == DialogResult.OK)
{
if (dlg.m_selected == null)
return;
buttonStopCurentTimer_Click(null, null);
m_currentTimer = dlg.m_selected;
string sql = DBHelper.GenerateInsertTimeRecordSQL(m_currentTimer);
m_currentTimer.db_id = LocalSqllite.ExecSQLCommandScalar(sql);
// Create new stopwatch.
m_stopWatch = System.Diagnostics.Stopwatch.StartNew();
m_isPaused = false;
labelTimerDescription.Text = m_currentTimer.desc;
buttonPauseTimer.Enabled = true;
}
}
public static string GenerateUpdateTimeRecordSQL(TimerType data, TimeSpan elapsed)
{
StringBuilder sqlStatement = new StringBuilder();
try
{
string currentDateTime = DateToDBDateTime(DateTime.Now);
sqlStatement.AppendLine("UPDATE time_sheet SET ");
sqlStatement.AppendLine(string.Format("work_end_time = '{0}'", currentDateTime));
sqlStatement.AppendLine(string.Format(",mins_accrued = '{0}'", elapsed.TotalMinutes));
sqlStatement.AppendLine(string.Format("WHERE work_id = '{0}'", data.db_id));
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return sqlStatement.ToString();
}
public static string GenerateInsertTimeRecordSQL(TimerType data)
{
StringBuilder sqlStatement = new StringBuilder();
try
{
string startDate = DateToDBDate(DateTime.Now);
string startDateTime = DateToDBDateTime(DateTime.Now);
// insert statment
sqlStatement.AppendLine("INSERT OR IGNORE INTO time_sheet ( date, work_start_time, work_end_time, pmo_number ) VALUES ( ");
sqlStatement.AppendLine(string.Format("'{0}'", startDate));
sqlStatement.AppendLine(string.Format(",'{0}'", startDateTime));
sqlStatement.AppendLine(string.Format(",'{0}'", startDateTime));
sqlStatement.AppendLine(string.Format(",'{0}'", data.pmo_num));
sqlStatement.AppendLine(");");
sqlStatement.AppendLine("-- get id from insert");
sqlStatement.AppendLine("SELECT last_insert_rowid()");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return sqlStatement.ToString();
}