/// <summary>
/// 获取计划 （默认获取所有计划）
/// </summary>
/// <returns></returns>
public static List <string[]> SearchPlan(string sele = "SELECT * FROM PlanTB")
{
SQLiteDB.Open();
return(SQLiteDB.Run(sele, (list) => {
return SQLiteDB.SaveData(SQLiteDB.sdr, list);
}, new List <string[]>()));
}
/// <summary>
/// 刷新重复计划
/// </summary>
/// <returns></returns>
public static List <string[]> RefreshRepeatPlan()
{
List <string[]> RepeatPlans = SearchRepetitionDays();
foreach (string[] plan in RepeatPlans)
{
SQLiteDB.Open();
SQLiteDB.Run($"UPDATE PlanTB SET IsComplete = 0 WHERE ID = {plan[0]}");
SQLiteDB.Close();
}
return(RepeatPlans);
}
private void Btn_Add_Click(object sender, EventArgs e)
{
switch (Tools.JudgeTextBoxIsEmpty(panel1))
{
case "t_title":
MessageBox.Show("标题不能为空！");
return;
case "t_Instructions":
MessageBox.Show("正文不能为空！");
return;
}
if (t_title.Text.Length > 20)
{
MessageBox.Show("标题不能超过20个字");
return;
}
else if (t_Instructions.Text.Length > 200)
{
MessageBox.Show("内容不能超过200个字");
return;
}
if (check_Repeat.Checked && c_Condition1.SelectedIndex != 0 && RepeatDays.RepetitionDays == null)
{
MessageBox.Show("请选择重复日期!");
return;
}
string[] conds = CreateRepetitionCondition();
string 铃声路径 = String.IsNullOrEmpty(c_Bell.Text) ? "No Remind" : PlanSetting.Default.SoundFiles[c_Bell.SelectedIndex];
int IsRemind = r_Yes.Checked ? 1 : 0;
//插入记录
SQLiteDB.Open();
/*,datetime('{dtp_DateTime.Value.ToString("yyyy-MM-dd HH:mm")}')*/
int insertRows = SQLiteDB.Run($"INSERT INTO PlanTB VALUES(NULL,'{t_title.Text}','{dtp_DateTime.Value.ToString("yyyy-MM-dd HH:mm")}','{t_Instructions.Text}','{铃声路径}',{IsRemind},0,{conds[0]},'{conds[1]}')");
//int updateRows = SQLiteDB.Run($"INSERT INTO PlanTB VALUES(NULL,'{t_title.Text}')");
if (insertRows > 0)
{
MessageBox.Show("添加成功");
Tools.SearchForTodayPlan(PlanDatas, Plans, flp_Plan);
}
else
{
MessageBox.Show("添加失败");
}
SQLiteDB.Close();
}
private void Btn_Update_Click(object sender, EventArgs e)
{
if (t_title.Text.Length > 20)
{
MessageBox.Show("标题不能超过20个字");
return;
}
else if (rt_Instructions.Text.Length > 200)
{
MessageBox.Show("内容不能超过200个字");
return;
}
if (check_Repeat.Checked && c_Condition1.SelectedIndex != 0 && RepeatDays.RepetitionDays == null)
{
MessageBox.Show("请选择重复日期!");
return;
}
string[] conds = CreateRepetitionCondition();
DialogResult result = MessageBox.Show("确定保存更改吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
int IsRemind = r_Yes.Checked ? 1 : 0;
BellPath = PlanSetting.Default.SoundFiles[c_Bell.SelectedIndex];
SQLiteDB.Open();
int error = SQLiteDB.Run($"UPDATE PlanTB SET PlanName='{t_title.Text}',PlanDateTime='{dtp_DateTime.Value.ToString("yyyy-MM-dd HH:mm:ss")}',Instructions='{rt_Instructions.Text}',BellPath='{BellPath}',IsRemind={IsRemind},RepetitionPeriod={conds[0]},RepetitionDays='{conds[1]}' WHERE ID = {this.id}");
SQLiteDB.Close();
if (error > 0)
{
MessageBox.Show("修改成功");
Tools.SearchForTodayPlan(PlanDatas, Plans, flp_Plan);
}
else if (error == 0)
{
MessageBox.Show("什么都没有变动");
}
else
{
MessageBox.Show("修改失败");
}
}
}
/// <summary>
/// 将过去待完成的计划设置为未完成
/// </summary>
/// <param name="PlanDatas"></param>
/// <returns>未完成的计划</returns>
public static List <string[]> GetUncomplete()
{
/*IEnumerable<string[]> UncompelePlans = from plan in PlanDatas
* where DateTime.Parse(plan[2]).Date < DateTime.Now.Date && plan[6] == "0"
* select plan;*/
List <string[]> UnCompletePlans = SearchPlan($"SELECT * FROM PlanTB WHERE PlanDateTime < datetime('{DateTime.Now.Date}') and IsComplete = '0'");
if (UnCompletePlans != null)
{
foreach (string[] plan in UnCompletePlans)
{
SQLiteDB.Open();
SQLiteDB.Run($"UPDATE PlanTB SET IsComplete = -1 WHERE ID = {plan[0]}");
SQLiteDB.Close();
}
}
return(UnCompletePlans);
}
private void Btn_Del_Click(object sender, EventArgs e)
{
if (MessageBox.Show("确定删除?", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
SQLiteDB.Open();
if (SQLiteDB.Run($"DELETE FROM PlanTB WHERE ID = {id};") > 0)
{
MessageBox.Show("删除成功");
Tools.SearchForTodayPlan(PlanDatas, Plans, flp_Plan);
this.Close();
}
else
{
MessageBox.Show("删除失败");
}
SQLiteDB.Close();
}
}
void CreatePlanTB()
{
if (SQLiteDB.Open() == -1)
{
MessageBox.Show("SQLite数据库打开失败");
IsContinue = false;
return;
}
SQLiteDB.Run("CREATE TABLE if not exists PlanTB(" +
"ID INTEGER PRIMARY KEY AUTOINCREMENT," +
"PlanName varchar(50)," +
"PlanDateTime varchar(50)," +
"Instructions varchar(255)," +
"BellPath varchar(255)," +
"IsRemind INTEGER," +
"IsComplete integer," +
"RepetitionPeriod integer," +
"RepetitionDays varchar(255)" +
")");
SQLiteDB.Close();
}
/// <summary>
/// 搜索今日计划
/// </summary>
public static void SearchForTodayPlan(List <string[]> PlanDatas, List <p_Plan> Plans, FlowLayoutPanel flp_Plan)
{
Tools.PlanDataLock = true;
SQLiteDB.Open();
//2019-09-20 12:28:18.000
string DoW = DateTime.Now.DayOfWeek.ToString("d") == "0" ? "7" : DateTime.Now.DayOfWeek.ToString("d"); //判断是否为周日
DateTime dt1 = DateTime.Now.Date;
DateTime dt2 = dt1.AddDays(1); // 查询重复的
string sele = $"SELECT * FROM PlanTB WHERE datetime(PlanDateTime) >= datetime('{dt1.ToString("yyyy-MM-dd HH:mm")}') and datetime(PlanDateTime) <= datetime('{dt2.ToString("yyyy-MM-dd HH:mm")}') and RepetitionPeriod = -1 or RepetitionPeriod = 0 or RepetitionPeriod = 1 and RepetitionDays like '%,{DoW},%' or RepetitionPeriod = 2 and RepetitionDays like '%,{DateTime.Now.Day},%';";
//sele = "SELECT * FROM PlanTB";--
PlanDatas = SQLiteDB.Run(sele, (list) => {
return(SQLiteDB.SaveData(SQLiteDB.sdr, list));
}, PlanDatas);
SQLiteDB.Close();
//PlanDatas.AddRange(Tools.SearchRepetitionDays());
SQLiteDB.Close();
Tools.PlanDataLock = false;
Tools.ShowPlans(PlanDatas, Plans, flp_Plan);
}
/// <summary>
/// 显示对应日期的计划
/// </summary>
/// <param name="dt"></param>
public static void ShowPlans(dynamic PlanDatas, List <p_Plan> Plans, FlowLayoutPanel flp_Plan)
{
flp_Plan.Controls.Clear();
Plans.Clear();
if (PlanDatas != null)
{
for (int i = 0; i < PlanDatas.Count; i++)
{
Plans.Add(new p_Plan());
//DateTime jDateTime = DateTime.Parse(PlanDatas[i][2]);
//if (jDateTime.ToShortDateString() == DateTime.Now.ToShortDateString())//
//{
Plans[i].l_Title.Text = PlanDatas[i][1];
Plans[i].l_DateTime.Text = PlanDatas[i][2];
Plans[i].t_Instructions.Text = PlanDatas[i][3];
Plans[i].t_Instructions.ScrollBars = ScrollBars.Both;
Plans[i].BorderStyle = BorderStyle.FixedSingle;
if (int.Parse(PlanDatas[i][5]) == 1)
{
Plans[i].IsRemind.Checked = true;
}
if (int.Parse(PlanDatas[i][7]) != -1)
{
Plans[i].check_Repeat.Checked = true;
}
//设置计划panel背景颜色
if (int.Parse(PlanDatas[i][6]) == 1) //完成
{
Plans[i].BackColor = Color.Blue;
}
else if (int.Parse(PlanDatas[i][6]) == -1) //未完成
{
Plans[i].BackColor = Color.Red;
}
else //待完成
{
Plans[i].BackColor = Color.FromArgb(246, 245, 248);
}
flp_Plan.Controls.Add(Plans[i]);
Plans[i].Click += OpenPlan;
Plans[i].l_Title.Click += OpenPlan;
Plans[i].l_DateTime.Click += OpenPlan;
///打开计划
void OpenPlan(Object sender, EventArgs e)
{
if (pf != null && !PlanForm.IsClose)
{
DialogResult result = MessageBox.Show("已经打开了一个计划窗口，继续此操作将会关闭已打开的窗口，是否继续？", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.No)
{
return;
}
pf.Close();
}
int index = flp_Plan.Controls.IndexOf((Control)sender);
if (index == -1)
{
index = flp_Plan.Controls.IndexOf(((Control)sender).Parent);
}
pf = new PlanForm(PlanDatas[index], PlanDatas, Plans, flp_Plan);
pf.Show();
}
//完成
Plans[i].btn_Complete.Click += (sender, e) => {
//MessageBox.Show("complete"+flp_Plan.Controls.IndexOf(((Button)sender).Parent).ToString());
int index = flp_Plan.Controls.IndexOf(((Button)sender).Parent);
DateTime now = DateTime.Now.Date;
if (Plans[index].BackColor != Color.Blue && now <= DateTime.Parse(PlanDatas[index][2]) || Plans[index].BackColor != Color.Blue && PlanDatas[index][7] != "-1")
{
Plans[index].BackColor = Color.Blue;
SQLiteDB.Open();
SQLiteDB.Run($"UPDATE PlanTB SET IsComplete = 1 WHERE ID = {PlanDatas[index][0]}");
SQLiteDB.Close();
}
};
//未完成
Plans[i].btn_Unfinished.Click += (sender, e) => {
int index = flp_Plan.Controls.IndexOf(((Button)sender).Parent);
DateTime now = DateTime.Now.Date;
if (Plans[index].BackColor != Color.Red && now <= DateTime.Parse(PlanDatas[index][2]) || Plans[index].BackColor != Color.Blue && PlanDatas[index][7] != "-1")
{
Plans[index].BackColor = Color.Red;
SQLiteDB.Open();
SQLiteDB.Run($"UPDATE PlanTB SET IsComplete = -1 WHERE ID = {PlanDatas[index][0]}");
SQLiteDB.Close();
}
};
//}
}
}
}