public static Boolean ExecuteUpdateTable(string dbpath, ListViewFile lvFile)
{
Boolean ret = false;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand("UPDATE tasklist set name=@name, description=@description, priority=@priority, createdate=@createdate, duedate=@duedate, status=@status, workholder=@workholder where id=@id", con);
com.Parameters.Add(sqliteParamInt64(com, "@id", lvFile.Id));
com.Parameters.Add(sqliteParam(com, "@name", lvFile.Name));
com.Parameters.Add(sqliteParam(com, "@description", lvFile.Description));
com.Parameters.Add(sqliteParam(com, "@priority", lvFile.Priority));
com.Parameters.Add(sqliteParam(com, "@createdate", DateCalc.getUTCString(lvFile.CreateDate, System.Globalization.CultureInfo.CurrentCulture)));
com.Parameters.Add(sqliteParam(com, "@duedate", DateCalc.getUTCString(lvFile.DueDate, System.Globalization.CultureInfo.CurrentCulture)));
com.Parameters.Add(sqliteParam(com, "@status", lvFile.Status));
com.Parameters.Add(sqliteParam(com, "@workholder", lvFile.WorkHolder));
try
{
com.ExecuteNonQuery();
ret = true;
}
catch (Exception ex)
{
MessageBox.Show("database table update error!\n" + ex.Message);
}
finally
{
con.Close();
}
return(ret);
}
private void ButtonDateSumDateWindowOk_Click(object sender, RoutedEventArgs e)
{
DateTime due = (DateTime)DateSumDateBox.SelectedDate;
dateString = DateCalc.DateToString(due, System.Globalization.CultureInfo.CurrentCulture);
// Dialog box accepted; ウィンドウを閉じる
this.DialogResult = true;
}
public static Boolean ExecuteSelectDateDetailsTableTaskTime(string dbpath, DateDetailsViewModel dsv, string sql, DateTime dt)
{
// return value true: More button visibie
Boolean ret = false;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand(sql, con);
com.Parameters.Add(sqliteParam(com, "@date", dt.Date.ToString("yyyy-MM-dd HH:mm:ss")));
try
{
SQLiteDataReader sdr = com.ExecuteReader();
int resultCount = 0;
while (sdr.Read() == true)
{
resultCount++;
if (resultCount <= DateDetailsMoreSize)
{
ListDateDetails lds = new ListDateDetails();
lds.Name = (string)sdr["name"];
DateTime utc = DateCalc.SQLiteStringToDateTime((string)sdr["start_date"]);
lds.StartDate = utc.ToLocalTime().ToString("G", System.Globalization.CultureInfo.CurrentCulture);
DateTime utc2 = DateCalc.SQLiteStringToDateTime((string)sdr["end_date"]);
lds.EndDate = utc2.ToLocalTime().ToString("G", System.Globalization.CultureInfo.CurrentCulture);
TimeSpan ts = new TimeSpan((Int64)sdr["duration"]);
lds.Duration = ts.ToString(@"hh\:mm\:ss");
dsv.Entries.Add(lds);
SumTimeSpanDateDetails += ts;
}
else
{
DateDetailsMoreCount += DateDetailsMoreSize;
ret = true;
}
}
sdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("database table select tasktime DateDetails error!\n" + ex.Message);
}
finally
{
con.Close();
}
return(ret);
}
public DateSumWindow()
{
InitializeComponent();
this.DataContext = DateSumViewModel.dsv;
DateTime dt = DateTime.Today;
textboxDateSum.Text = DateCalc.DateToString(dt, System.Globalization.CultureInfo.CurrentCulture);
ExecuteFirstSelectTableTaskTime(dt.Date);
}
/// <summary>
/// more button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DateSumMoreButton_Click(object sender, RoutedEventArgs e)
{
if (SQLiteClass.ExecuteMoreSelectTableTaskTime(SQLiteClass.dbpath, DateCalc.StringToDate(textboxDateSum.Text, System.Globalization.CultureInfo.CurrentCulture)))
{
DateSumMoreButton.Visibility = Visibility.Visible;
textDateSumStatusBar.Content = "";
}
else
{
DateSumMoreButton.Visibility = Visibility.Collapsed;
textDateSumStatusBar.Content = Properties.Resources.DD_Sum + SQLiteClass.SumTimeSpanDateSum.ToString(@"hh\:mm\:ss");
}
}
public static Boolean ExecuteSelectTableTaskMemo(string dbpath, TaskMemoViewModel tmv, string sql, Int64 id)
{
// return value true: More button visibie
Boolean ret = false;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand(sql, con);
com.Parameters.Add(sqliteParamInt64(com, "@id", id));
try
{
SQLiteDataReader sdr = com.ExecuteReader();
int resultCount = 0;
while (sdr.Read() == true)
{
resultCount++;
if (resultCount <= TaskMemoMoreSize)
{
ListTaskMemo ltm = new ListTaskMemo();
ltm.Memo = (string)sdr["memo"];
DateTime utc = DateCalc.SQLiteStringToDateTime((string)sdr["date"]);
ltm.Date = utc.ToLocalTime().ToString("G", System.Globalization.CultureInfo.CurrentCulture);
tmv.Memos.Add(ltm);
}
else
{
TaskMemoMoreCount += TaskMemoMoreSize;
ret = true;
}
}
sdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("database table select taskmemo error!\n" + ex.Message);
}
finally
{
con.Close();
}
return(ret);
}
/// <summary>
/// date change button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonDateSumChange_Click(object sender, RoutedEventArgs e)
{
// Instantiate the dialog box
DateSumDateWindow dlg = new DateSumDateWindow();
// Configure the dialog box
dlg.Owner = this;
dlg.WindowStartupLocation = WindowStartupLocation.CenterOwner;
dlg.dateString = textboxDateSum.Text;
// Open the dialog box modally
if (dlg.ShowDialog() == true)
{
// due date window is closed
textboxDateSum.Text = dlg.dateString;
ExecuteFirstSelectTableTaskTime(DateCalc.StringToDate(textboxDateSum.Text, System.Globalization.CultureInfo.CurrentCulture));
}
}
private void Name_Click(object sender, RoutedEventArgs e)
{
SQLiteClass.DateSumNameOrderByDirection = SQLiteClass.toggleDirection(SQLiteClass.DateSumNameOrderByDirection);
SQLiteClass.DateSumSetOrderBy("name", SQLiteClass.DateSumNameOrderByDirection);
ExecuteFirstSelectTableTaskTime(DateCalc.StringToDate(textboxDateSum.Text, System.Globalization.CultureInfo.CurrentCulture));
}
private void DurationDes_Click(object sender, RoutedEventArgs e)
{
SQLiteClass.DateSumNameOrderByDirection = "DESC";
SQLiteClass.DateSumSetOrderBy("duration", "DESC");
ExecuteFirstSelectTableTaskTime(DateCalc.StringToDate(textboxDateSum.Text, System.Globalization.CultureInfo.CurrentCulture));
}
private void DateSumDateWindow_Loaded(object sender, RoutedEventArgs e)
{
DateTime due = DateCalc.StringToDate(dateString, System.Globalization.CultureInfo.CurrentCulture);
DateSumDateBox.SelectedDate = due;
}
