public static tblTasks selectTaskById(string id) { tblTasks result = new tblTasks(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "select id, task_name,task_name_en, task_type, areas, location " + ",plan_date_start" + ",plan_date_end" + ",plan_person,note" + ",if(copy_f=1,'true','false') as copy_f" + " from tbl_tasks" + " where del_f = 0" + " and id = " + id; cmd.Prepare(); using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { result.id = reader["id"]; result.task_name = reader["task_name"]; result.task_name_en = reader["task_name_en"]; result.task_type = reader["task_type"]; result.areas = reader["areas"]; result.location = reader["location"]; result.plan_date_start = reader["plan_date_start"]; result.plan_date_end = reader["plan_date_end"]; result.plan_person = reader["plan_person"]; result.note = reader["note"]; result.copy_f = reader["copy_f"]; } } return(result); }
internal static void updateById(tblTasks task) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "UPDATE tbl_tasks SET" + " task_name = @task_name," + " task_name_en = @task_name_en," + " task_type = @task_type," + " areas = @areas," + " location = @location," + " plan_date_start = @plan_date_start," + " plan_date_end = @plan_date_end," + " plan_person = @plan_person," + " re_plan_date_start = @re_plan_date_start," + " re_plan_date_end = @re_plan_date_end," + " person = @person," + " must_date_finish = @must_date_finish," + " status = @status," + " note = @note," + " date_finish = @date_finish," + " copy_f = @copy_f," + " del_f = @del_f " + " WHERE id = " + task.id; cmd.Prepare(); cmd.Parameters.AddWithValue("@task_name", task.task_name); cmd.Parameters.AddWithValue("@task_name_en", task.task_name_en); cmd.Parameters.AddWithValue("@task_type", task.task_type); cmd.Parameters.AddWithValue("@areas", task.areas); cmd.Parameters.AddWithValue("@location", task.location); cmd.Parameters.AddWithValue("@plan_date_start", task.plan_date_start); cmd.Parameters.AddWithValue("@plan_date_end", task.plan_date_end); cmd.Parameters.AddWithValue("@plan_person", task.plan_person); cmd.Parameters.AddWithValue("@re_plan_date_start", task.re_plan_date_start); cmd.Parameters.AddWithValue("@re_plan_date_end", task.re_plan_date_end); cmd.Parameters.AddWithValue("@person", task.person); cmd.Parameters.AddWithValue("@must_date_finish", task.must_date_finish); cmd.Parameters.AddWithValue("@status", task.status); cmd.Parameters.AddWithValue("@note", task.note); cmd.Parameters.AddWithValue("@date_finish", task.date_finish); cmd.Parameters.AddWithValue("@copy_f", task.copy_f); cmd.Parameters.AddWithValue("@del_f", task.del_f); cmd.ExecuteNonQuery(); }
public static List <tblTasks> selectTaskListPlan(DateTime fromDate, DateTime toDate) { string from = fromDate.ToString("yyyy-MM-dd"); string to = toDate.ToString("yyyy-MM-dd"); List <tblTasks> result = new List <tblTasks>(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "select tbl_tasks.id as id,task_name,task_type_name" + ",task_name_en, tbl_areas.areas as areas, location" + ",DATE_FORMAT(plan_date_start,'%d-%m-%Y') as plan_date_start" + ",DATE_FORMAT(plan_date_end,'%d-%m-%Y') as plan_date_end" + ",plan_person,note" + ",if(copy_f=1,'yes','no') as copy_f" + " from tbl_tasks" + " LEFT JOIN tbl_task_type ON tbl_tasks.task_type = tbl_task_type.id" + " LEFT JOIN tbl_areas ON tbl_tasks.areas = tbl_areas.id" + " where del_f = 0" + " and (date_format(plan_date_start,'%Y-%m-%d') between '" + from + "' " + " and '" + to + "')" + " order by plan_date_start asc, id asc"; cmd.Prepare(); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { tblTasks tblRecord = new tblTasks(); tblRecord.id = reader["id"]; tblRecord.task_name = reader["task_name"]; tblRecord.task_name_en = reader["task_name_en"]; tblRecord.task_type_name = reader["task_type_name"]; tblRecord.areas = reader["areas"]; tblRecord.location = reader["location"]; tblRecord.plan_date_start = reader["plan_date_start"]; tblRecord.plan_date_end = reader["plan_date_end"]; tblRecord.plan_person = reader["plan_person"]; tblRecord.note = reader["note"]; tblRecord.copy_f = reader["copy_f"]; result.Add(tblRecord); } } return(result); }
internal static void updateRePlanById(tblTasks task) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "UPDATE tbl_tasks SET" + " re_plan_date_start = @re_plan_date_start," + " re_plan_date_end = @re_plan_date_end," + " person = @person," + " must_date_finish = @must_date_finish," + " status = @status," + " note = @note" + " WHERE id = " + task.id; cmd.Prepare(); cmd.Parameters.AddWithValue("@re_plan_date_start", task.re_plan_date_start); cmd.Parameters.AddWithValue("@re_plan_date_end", task.re_plan_date_end); cmd.Parameters.AddWithValue("@person", task.person); cmd.Parameters.AddWithValue("@must_date_finish", task.must_date_finish); cmd.Parameters.AddWithValue("@status", task.status); cmd.Parameters.AddWithValue("@note", task.note); cmd.ExecuteNonQuery(); }
public static tblTasks selectTaskStatusById(string id) { tblTasks result = new tblTasks(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "select id, task_name,task_name_en, task_type, areas, location " + ",re_plan_date_start" + ",re_plan_date_end" + ",plan_person,person, must_date_finish, note" + ",status as status" + " from tbl_tasks" + " where del_f = 0" + " and id = " + id; cmd.Prepare(); using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { result.id = reader["id"]; result.task_name = reader["task_name"]; result.task_name_en = reader["task_name_en"]; result.task_type = reader["task_type"]; result.areas = reader["areas"]; result.location = reader["location"]; result.re_plan_date_start = reader["re_plan_date_start"]; result.re_plan_date_end = reader["re_plan_date_end"]; result.plan_person = reader["plan_person"]; result.person = reader["person"]; result.must_date_finish = reader["must_date_finish"]; result.note = reader["note"]; result.status = reader["status"]; } } return(result); }
public static List <tblTasks> selectAllSample() { List <tblTasks> result = new List <tblTasks>(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "select * from tbl_tasks"; cmd.Prepare(); // int result = command.ExecuteNonQuery(); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { tblTasks tblRecord = new tblTasks(); tblRecord.id = reader["id"]; tblRecord.task_name = reader["task_name"]; tblRecord.task_type = reader["task_type"]; tblRecord.plan_date_start = reader["plan_date_start"]; tblRecord.plan_date_end = reader["plan_date_end"]; tblRecord.plan_person = reader["plan_person"]; tblRecord.re_plan_date_start = reader["re_plan_date_start"]; tblRecord.re_plan_date_end = reader["re_plan_date_end"]; tblRecord.person = reader["person"]; tblRecord.must_date_finish = reader["must_date_finish"]; tblRecord.status = reader["status"]; tblRecord.note = reader["note"]; tblRecord.date_finish = reader["date_finish"]; tblRecord.copy_f = reader["copy_f"]; tblRecord.del_f = reader["del_f"]; result.Add(tblRecord); //Console.WriteLine(String.Format("{0}-{1}", reader["id"], reader["task_type_name"])); } } return(result); }
internal static List <tblTasks> selectTaskList(DateTime toDate, bool finish_f) { string to = toDate.ToString("yyyy-MM-dd"); List <tblTasks> result = new List <tblTasks>(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; String sql = " SELECT " + " id, " + " task_name, " + " task_name_en, " + " task_type_name, " + " areas, " + " location, " + " plan_person, " + " re_plan_date_start, " + " re_plan_date_end, " + " person, " + " must_date_finish, " + " status, " + " delay, " + " note " + " FROM " + " ( " + " SELECT " + " tbl_tasks.id AS id, " + " task_name, " + " task_name_en, " + " task_type_name, " + " tbl_areas.areas as areas, " + " location, " + " plan_person, " + " DATE_FORMAT(re_plan_date_start, '%d-%m-%Y') AS re_plan_date_start, " + " DATE_FORMAT(re_plan_date_end, '%d-%m-%Y') AS re_plan_date_end, " + " person, " + " DATE_FORMAT(must_date_finish, '%d-%m-%Y') AS must_date_finish, " + " (CASE " + " WHEN status = 1 THEN 'doing' " + " WHEN status = 9 THEN 'done' " + " ELSE '' " + " END) AS status, " + " if(DATE_FORMAT(must_date_finish, '%Y-%m-%d') < DATE_FORMAT(sysdate(), '%Y-%m-%d') AND status <> 9 ,'delay','') as delay, " + " note " + " FROM " + " tbl_tasks " + " LEFT JOIN tbl_task_type ON tbl_tasks.task_type = tbl_task_type.id" + " LEFT JOIN tbl_areas ON tbl_tasks.areas = tbl_areas.id " + " WHERE del_f = 0 " + " AND DATE_FORMAT(re_plan_date_start, '%Y-%m-%d') <= '" + to + "' "; // + " OR ( DATE_FORMAT(must_date_finish, '%Y-%m-%d') < DATE_FORMAT(sysdate(), '%Y-%m-%d') AND status <> 9 ) ) "; if (!finish_f) { sql = sql + " AND status <> 9 "; } sql = sql + " ) AS TMP " + " ORDER BY delay DESC, re_plan_date_start ASC, id ASC"; cmd.CommandText = sql; cmd.Prepare(); using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { tblTasks tblRecord = new tblTasks(); tblRecord.id = reader["id"]; tblRecord.task_name = reader["task_name"]; tblRecord.task_name_en = reader["task_name_en"]; tblRecord.task_type_name = reader["task_type_name"]; tblRecord.areas = reader["areas"]; tblRecord.location = reader["location"]; tblRecord.plan_person = reader["plan_person"]; tblRecord.re_plan_date_start = reader["re_plan_date_start"]; tblRecord.re_plan_date_end = reader["re_plan_date_end"]; tblRecord.person = reader["person"]; tblRecord.must_date_finish = reader["must_date_finish"]; tblRecord.status = reader["status"]; tblRecord.delay = reader["delay"]; tblRecord.note = reader["note"]; result.Add(tblRecord); } } return(result); }
public static void insert(tblTasks task) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = Program.con; cmd.CommandText = "INSERT INTO tbl_tasks (" + "task_name, " + "task_name_en, " + "task_type, " + "areas, " + "location, " + "plan_date_start, " + "plan_date_end, " + "plan_person, " + "re_plan_date_start, " + "re_plan_date_end, " + "person, " + "must_date_finish, " + "status, " + "note, " + "date_finish, " + "copy_f, " + "del_f " + ") VALUES(" + "@task_name, " + "@task_name_en, " + "@task_type, " + "@areas, " + "@location, " + "@plan_date_start, " + "@plan_date_end, " + "@plan_person, " + "@re_plan_date_start, " + "@re_plan_date_end, " + "@person, " + "@must_date_finish, " + "@status, " + "@note, " + "@date_finish, " + "@copy_f, " + "@del_f " + ")"; cmd.Prepare(); cmd.Parameters.AddWithValue("@task_name", task.task_name); cmd.Parameters.AddWithValue("@task_name_en", task.task_name_en); cmd.Parameters.AddWithValue("@task_type", task.task_type); cmd.Parameters.AddWithValue("@areas", task.areas); cmd.Parameters.AddWithValue("@location", task.location); cmd.Parameters.AddWithValue("@plan_date_start", task.plan_date_start); cmd.Parameters.AddWithValue("@plan_date_end", task.plan_date_end); cmd.Parameters.AddWithValue("@plan_person", task.plan_person); cmd.Parameters.AddWithValue("@re_plan_date_start", task.re_plan_date_start); cmd.Parameters.AddWithValue("@re_plan_date_end", task.re_plan_date_end); cmd.Parameters.AddWithValue("@person", task.person); cmd.Parameters.AddWithValue("@must_date_finish", task.must_date_finish); cmd.Parameters.AddWithValue("@status", task.status); cmd.Parameters.AddWithValue("@note", task.note); cmd.Parameters.AddWithValue("@date_finish", task.date_finish); cmd.Parameters.AddWithValue("@copy_f", task.copy_f); cmd.Parameters.AddWithValue("@del_f", task.del_f); cmd.ExecuteNonQuery(); }