public static Boolean ExecuteInsertTableFTSString(string dbpath, Int64 id, string type, string str)
{
Boolean ret = false;
SQLiteConnection con = new SQLiteConnection("Data Source=" + dbpath + ";");
con.Open();
SQLiteCommand com = new SQLiteCommand("INSERT INTO strings_fts (id, type, str) VALUES (@id, @type, @str)", con);
com.Parameters.Add(sqliteParamInt64(com, "@id", id));
com.Parameters.Add(sqliteParam(com, "@type", type));
com.Parameters.Add(sqliteParam(com, "@str", Ngram.getNgramText(str, 2)));
try
{
com.ExecuteNonQuery();
ret = true;
}
catch (Exception ex)
{
MessageBox.Show("database table strings_fts insert error!\n" + ex.Message);
}
finally
{
con.Close();
}
return(ret);
}
/// <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);
}
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);
}
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;
}
/// <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;
}