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);
}
public static int AutoGenerateTask(string dbpath, ListAutoGenerate entry, DateTime theDate)
{
int result = 0;
DateTime dueDate = new DateTime(theDate.Year, theDate.Month, theDate.Day, (int)entry.Due_hour, (int)entry.Due_minute, 0);
if (TimeZoneInfo.Local.IsInvalidTime(dueDate))
{
dueDate = new DateTime(theDate.Year, theDate.Month, theDate.Day, 0, 0, 0);
}
ListViewFile lvFile = new ListViewFile()
{
Name = entry.Name,
Description = "",
Memo = "",
Priority = entry.Priority,
DueDate = TimeZoneInfo.ConvertTimeToUtc(dueDate).ToString("yyyy-MM-dd HH:mm:ss"),
Status = "Active",
WorkHolder = WorkHolder.CreateWorkHolder(entry.Name)
};
if (entry.Template > 0)
{
var tlv = new TemplateListViewModel();
if (SQLiteClass.ExecuteSelectATableTemplate(dbpath, tlv, entry.Template))
{
if (!string.IsNullOrEmpty(tlv.Entries[0].Template))
{
lvFile.Description = tlv.Entries[0].Template;
result += 1;
}
}
}
Int64 retId;
retId = SQLiteClass.ExecuteInsertTable(dbpath, lvFile);
if (retId > 0)
{
result += 2;
}
if (SQLiteClass.ExecuteInsertTableFTSString(dbpath, retId, "tasklist_name", Ngram.getNgramText(lvFile.Name, 2)))
{
result += 4;
}
if (SQLiteClass.ExecuteInsertTableFTSString(dbpath, retId, "tasklist_description", Ngram.getNgramText(lvFile.Description, 2)))
{
result += 8;
}
// copy template path
if (entry.Template > 0)
{
string workHolder = WorkHolder.CreateWorkHolder(entry.Name);
if (WorkHolder.CopyTemplatePathToWorkFolder(dbpath, entry.Template, workHolder) > 0)
{
result += 16;
}
}
return(result);
}
/// <summary>
/// Execute select table
/// </summary>
/// <param name="mv"></param>
/// <param name="sql"></param>
/// <returns>return value true: More button visibie</returns>
public static Boolean ExecuteSelectTable(string dbpath, MainViewModel mv, string sql, string searchString)
{
Boolean ret = false;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand(sql, con);
if (!String.IsNullOrEmpty(searchString))
{
com.Parameters.Add(sqliteParam(com, "@str", Ngram.getNgramTextSpaceSeparated(searchString, 2)));
}
try
{
SQLiteDataReader sdr = com.ExecuteReader();
int resultCount = 0;
while (sdr.Read() == true)
{
resultCount++;
if (resultCount <= moreSize)
{
ListViewFile lvFile = new ListViewFile();
lvFile.Id = (Int64)sdr["id"];
lvFile.Name = (string)sdr["name"];
lvFile.Description = (string)sdr["description"];
lvFile.Memo = (string)sdr["memo"];
lvFile.Priority = (string)sdr["priority"];
DateTime utc = (DateTime)sdr["createdate"];
lvFile.CreateDate = utc.ToLocalTime().ToString("G", System.Globalization.CultureInfo.CurrentCulture);
DateTime utc2 = (DateTime)sdr["duedate"];
lvFile.DueDate = utc2.ToLocalTime().ToString("G", System.Globalization.CultureInfo.CurrentCulture);
lvFile.Status = (string)sdr["status"];
lvFile.WorkHolder = (string)sdr["workholder"];
mv.Files.Add(lvFile);
}
else
{
moreCount += moreSize;
ret = true;
}
}
sdr.Close();
}
catch (Exception ex)
{
MessageBox.Show("database table select error!\n" + ex.Message);
}
finally
{
con.Close();
}
return(ret);
}
/// <summary>
/// list item is clicked, editpanel will be visible
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
void ListSelectionChanged(object sender, SelectionChangedEventArgs args)
{
ListViewFile lbf = ((sender as ListBox).SelectedItem as ListViewFile);
if (lbf == null)
{
// nop
}
else
{
refreshTimer_stop();
listviewTopToolbar.Visibility = Visibility.Collapsed;
listviewBottomToolbar.Visibility = Visibility.Collapsed;
listview1.Visibility = Visibility.Collapsed;
int priorityLen = lbf.Priority.Length;
if (priorityLen > 5)
{
priorityLen = 5;
}
epId = lbf.Id;
ep_name.Text = lbf.Name;
ep_description.Text = "";
//ep_memo.Text = "";
ep_priorityBox.SelectedIndex = 5 - priorityLen;
ep_createdate.Text = lbf.CreateDate;
ep_duedate.Text = lbf.DueDate;
dueDateColorConvert();
ep_statusBox.Text = lbf.Status;
editpanel.Visibility = Visibility.Visible;
workHolder = lbf.WorkHolder;
editTimer_start(epId);
// Memo
listviewTaskMemo.DataContext = TaskMemoViewModel.tmv;
ExecuteFirstSelectTableTaskMemo(epId);
// HyperLink
tmpDescription = lbf.Description;
HyperLink.FillHyperLinks(ep_description, HyperLink.CreateHyperLinkList(lbf.Description));
taskChanged = false;
}
}
public static Int64 ExecuteInsertTable(string dbpath, ListViewFile lvFile)
{
object obj;
Int64 retId;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand("INSERT INTO tasklist (name, description, memo, priority, createdate, duedate, status, workholder) VALUES (@name, @description, @memo, @priority, @createdate, @duedate, @status, @workholder); SELECT last_insert_rowid();", con);
com.Parameters.Add(sqliteParam(com, "@name", lvFile.Name));
com.Parameters.Add(sqliteParam(com, "@description", lvFile.Description));
com.Parameters.Add(sqliteParam(com, "@memo", lvFile.Memo));
com.Parameters.Add(sqliteParam(com, "@priority", lvFile.Priority));
com.Parameters.Add(sqliteParam(com, "@createdate", System.DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss")));
com.Parameters.Add(sqliteParam(com, "@duedate", lvFile.DueDate));
com.Parameters.Add(sqliteParam(com, "@status", lvFile.Status));
com.Parameters.Add(sqliteParam(com, "@workholder", lvFile.WorkHolder));
try
{
obj = com.ExecuteScalar();
retId = Int64.Parse(obj.ToString());
}
catch (Exception ex)
{
MessageBox.Show("database table insert error!\n" + ex.Message);
retId = 0;
}
finally
{
con.Close();
}
return(retId);
}
/// <summary>
/// editpanel Save button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ep_save_Click(object sender, RoutedEventArgs e)
{
ep_save.IsEnabled = false;
ep_close.IsEnabled = false;
editTimer_stop();
// TaskTimeInserted is false then INSERT
// else UPDATE the tasktime table
SQLiteClass.TaskTimeInserted = InsertOrUpdateTaskTime(
SQLiteClass.TaskTimeInserted,
SQLiteClass.tasklist_id,
SQLiteClass.editTimerStartDateTime);
ListViewFile lbf = new ListViewFile();
lbf.Id = epId;
lbf.Name = ep_name.Text;
lbf.Description = tmpDescription;
lbf.Priority = String.Concat(Enumerable.Repeat("\u272e", 5 - ep_priorityBox.SelectedIndex));
lbf.CreateDate = ep_createdate.Text;
lbf.DueDate = ep_duedate.Text;
lbf.Status = ep_statusBox.Text;
lbf.WorkHolder = workHolder;
if (SQLiteClass.ExecuteUpdateTable(SQLiteClass.dbpath, lbf))
{
SQLiteClass.ExecuteUpdateTableFTSString(SQLiteClass.dbpath, epId, "tasklist_name", Ngram.getNgramText(ep_name.Text, 2));
SQLiteClass.ExecuteUpdateTableFTSString(SQLiteClass.dbpath, epId, "tasklist_description", Ngram.getNgramText(tmpDescription, 2));
ep_CloseWindow();
ExecuteFirstSelectTable();
}
ep_save.IsEnabled = true;
ep_close.IsEnabled = true;
}
private void ButtonNewWindowOk_Click(object sender, RoutedEventArgs e)
{
Int64 retId;
NewWindowOk.IsEnabled = false;
// template description update check
var selectedTemplate = (ListTemplate)templateBox.SelectedItem;
if ((selectedTemplate.Id > 0) &&
(!string.IsNullOrEmpty(NewDescriptionBox.Text)) &&
(!string.IsNullOrEmpty(selectedTemplate.Template)))
{
var result = MessageBox.Show(Properties.Resources.WH_AlreadyExists, "taskalu",
MessageBoxButton.YesNo, MessageBoxImage.Warning);
if (result == MessageBoxResult.No)
{
NewWindowOk.IsEnabled = true;
return;
}
}
DateTime due = (DateTime)dateBox.SelectedDate;
DateTime dueDate = new DateTime(due.Year, due.Month, due.Day, hourBox.SelectedIndex, (minuteBox.SelectedIndex * 5), 0);
if (TimeZoneInfo.Local.IsInvalidTime(dueDate))
{
MessageBox.Show(Properties.Resources.MW_InvalidDate);
}
else
{
ListViewFile lvFile = new ListViewFile()
{
Name = NewTitleBox.Text,
Description = NewDescriptionBox.Text,
Memo = "",
Priority = priorityBox.Text,
DueDate = TimeZoneInfo.ConvertTimeToUtc(dueDate).ToString("yyyy-MM-dd HH:mm:ss"),
Status = "Active",
WorkHolder = WorkHolder.CreateWorkHolder(NewTitleBox.Text)
};
if (!string.IsNullOrEmpty(selectedTemplate.Template))
{
lvFile.Description = selectedTemplate.Template;
}
retId = SQLiteClass.ExecuteInsertTable(SQLiteClass.dbpath, lvFile);
SQLiteClass.ExecuteInsertTableFTSString(SQLiteClass.dbpath, retId, "tasklist_name", Ngram.getNgramText(NewTitleBox.Text, 2));
SQLiteClass.ExecuteInsertTableFTSString(SQLiteClass.dbpath, retId, "tasklist_description", Ngram.getNgramText(NewDescriptionBox.Text, 2));
// copy template path
if (selectedTemplate.Id > 0)
{
string workHolder = WorkHolder.CreateWorkHolder(NewTitleBox.Text);
WorkHolder.CopyTemplatePathToWorkFolder(SQLiteClass.dbpath, selectedTemplate.Id, workHolder);
}
// Dialog box accepted; ウィンドウを閉じる
this.DialogResult = true;
}
NewWindowOk.IsEnabled = true;
}
