public static Boolean addEvent(Event evnt)
{
try
{
DBConnection.updateDB("insert into event (proj_id, visit_type_id, to_date_time, from_date_time, sch_no, feedback, Other, to_do_list, resource, check_list, travelling_mode, accommodation_mode, meals) " +
" values (" + evnt.EventProject.ProjectID + ", " + evnt.Type.EventTypeId + ", '" + evnt.To.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + evnt.From.ToString("yyyy-MM-dd HH:mm:ss") +
"', " + evnt.ScheduleId.ScheduleId + ", '" + evnt.Feedback + "', '" + evnt.Other + "', '" + evnt.TodoList + "', '" + evnt.Resource + "', '" + evnt.Checklist + "', '" + evnt.TravelMode + "', '" +
evnt.AccommodationMode + "', '" + evnt.Meals + "' )");
foreach (var ary in evnt.ServEngineer)
{
EventTechnician et = (EventTechnician)ary;
DBConnection.updateDB("insert into event_technicians (event_id, staff_id, feedback, proj_id, sch_no) values (" + et.EventOfTechnician.EventId + ", " + et.Technician.StaffId +
", '" + et.Feedback + "', " + evnt.EventProject.ProjectID + ", " + evnt.ScheduleId.ScheduleId + ");");
MySqlDataReader reader = DBConnection.getData("select event_staff_id from event_technicians where staff_id = " + et.Technician.StaffId + " and event_id = " + et.EventOfTechnician.EventId +
" and proj_id = " + evnt.EventProject.ProjectID + " and sch_no = " + evnt.ScheduleId.ScheduleId + ";");
if (reader.Read())
{
int esi = reader.GetInt16("event_staff_id");
reader.Close();
DBConnection.updateDB("insert into event_technician_task (event_tech_id, task) values (" + esi + ", '" + et.Task + "');");
}
}
return(true);
}
catch (Exception e)
{
MessageBox.Show("Something went wrong!\n" + e.GetType(), "Add Event", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
private Event getEventRow(int proj_id, int sch_no, int event_id)
{
Event evnt = new Event();
try
{
MySqlDataReader reader = DBConnection.getData("select e.event_id, e.proj_id, e.visit_type_id, e.to_date_time, e.from_date_time, e.sch_no, e.feedback, e.Other, e.to_do_list, e.resource, e.check_list, e.travelling_mode, e.accommodation_mode, e.meals, p.client_id " +
" from event e, project p " +
" where e.proj_id = p.proj_id and e.event_id = " + event_id + " and e.proj_id = " + proj_id + " and e.sch_no = " + sch_no + ";");
if (reader.Read())
{
evnt.EventId = event_id;
evnt.EventProject = new Project(proj_id, reader.GetInt16("client_id"));
evnt.ScheduleId = new Schedule(sch_no);
evnt.Type = new EventType(reader.GetInt16("visit_type_id"));
evnt.From = reader.GetDateTime("from_date_time");
evnt.To = reader.GetDateTime("to_date_time");
evnt.Feedback = reader.GetString("feedback");
evnt.Other = reader.GetString("other");
evnt.TodoList = reader.GetString("to_do_list");
//evnt.Resource = reader.GetString("resource");
evnt.Checklist = reader.GetString("check_list");
evnt.TravelMode = reader.GetString("travelling_mode");
evnt.AccommodationMode = reader.GetString("accommodation_mode");
evnt.Meals = reader.GetString("meals");
reader.Close();
MySqlDataReader reader1 = DBConnection.getData("select s.staff_id, s.first_name, s.last_name, et.feedback, ett.task " +
"from event_technicians et, event_technician_task ett, staff s " +
"where (ett.event_tech_id = et.event_staff_id) and (s.staff_id = et.staff_id) and et.event_id = " + event_id + " and et.proj_id = " + proj_id + " and et.sch_no = " + sch_no + ";");
ArrayList serEng = new ArrayList();
while (reader1.Read())
{
EventTechnician et = new EventTechnician();
et.Technician = new Staff(reader1.GetInt16("staff_id"), reader1.GetString("first_name"), reader1.GetString("last_name"));
et.Task = reader1.GetString("task");
et.Feedback = reader1.GetString("feedback");
serEng.Add(et);
}
evnt.ServEngineer = serEng;
reader1.Close();
ArrayList resoArray = new ArrayList();
MySqlDataReader reader3 = DBConnection.getData("select sr.resource_id, sr.qty, r.name from event_resources sr, resource r where (sr.event_id = " + event_id + " and sr.sch_no =" + sch_no + " and sr.proj_id = " + proj_id + ") and (sr.resource_id = r.resource_id);");
while (reader3.Read())
{
Resource reso = new Resource();
reso.ResourceId = int.Parse(reader3.GetString("resource_id"));
reso.Name = reader3.GetString("name");
reso.TotalQty = int.Parse(reader3.GetString("qty"));
resoArray.Add(reso);
}
evnt.ResoArray = resoArray;
reader3.Close();
}
else
{
evnt = null;
}
}
catch (Exception)
{
MessageBox.Show("Something went wrong!");
throw;
}
return(evnt);
}
public void updateEvent()
{
Event evnt = new Event();
evnt.EventId = eventID;
evnt.EventProject = new Project(int.Parse(projectName.SelectedValue.ToString()), int.Parse(eventClientName.SelectedValue.ToString()));
evnt.ScheduleId = new Schedule(int.Parse(eventsSch.SelectedValue.ToString()));
evnt.Type = new EventType(int.Parse(scheduleType.SelectedValue.ToString()));
evnt.From = Convert.ToDateTime(eventStartDate.Text.ToString() + " " + eventStartTime.Text.ToString());
evnt.To = Convert.ToDateTime(eventEndDate.Text.ToString() + " " + eventEndTime.Text.ToString());
evnt.Feedback = overFeedback.Text;
evnt.Other = other.Text;
evnt.TodoList = todoList.Text;
evnt.Checklist = checkList.Text;
evnt.TravelMode = travelingMode.Text;
evnt.AccommodationMode = accomodation.Text;
evnt.Meals = meals.Text;
ArrayList eng = new ArrayList();
foreach (DataRow dr in feedbackGrid.Rows)
{
bool done = false;
foreach (EventTechnician etch in eng)
{
if (etch.Technician.StaffId == (int)dr[0])
{
etch.addTask(dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString());
done = true;
break;
}
}
if (!done)
{
eng.Add(new EventTechnician(null, new Staff((int)dr[0]), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString()));
}
}
foreach (DataRow dr in engGrid.Rows)
{
int engId = (int)dr[0];
bool exists = false;
foreach (EventTechnician et in eng)
{
if (et.Technician.StaffId == engId)
{
exists = true;
break;
}
}
if (!exists)
{
eng.Add(new EventTechnician(null, new Staff(engId), null));
}
}
evnt.ServEngineer = eng;
if (Database.updateEvent(evnt))
{
if (evntSendMail.Checked)
{
notifyIconEvent.Icon = SystemIcons.Application;
notifyIconEvent.BalloonTipText = "Sending Email!";
notifyIconEvent.ShowBalloonTip(1000);
foreach (var ary in evnt.ServEngineer)
{
EventTechnician et = (EventTechnician)ary;
MySqlDataReader reader = DBConnection.getData("select email, first_name, last_name from staff where staff_id = " + et.Technician.StaffId + " ");
reader.Read();
string subject = "New Event Change";
string body = "Dear " + reader.GetString("first_name") + " " + reader.GetString("last_name") +
"\n An event has been added or upfated for project " + projectName.Text.ToString() + ". Please check your events ASAP.";
Internet.sendMail(reader.GetString("email"), subject, body);
reader.Close();
}
notifyIconEvent.Icon = SystemIcons.Application;
notifyIconEvent.BalloonTipText = "Email(s) Sent!";
notifyIconEvent.ShowBalloonTip(1000);
}
MessageBox.Show("Event Successfully Updated !");
this.Close();
}
else
{
MessageBox.Show("Failed to update the event !");
this.Close();
}
}
public UpdateEventForm(Event evnt)
{
InitializeComponent();
et = evnt;
eventID = evnt.EventId;
projectName.DataSource = projectDataSource();
projectName.ValueMember = "proj_id";
projectName.DisplayMember = "proj_name";
eventClientName.DataSource = clientDataSource();
eventClientName.ValueMember = "client_id";
eventClientName.DisplayMember = "name";
scheduleType.DataSource = scheduleTypeDataSource();
scheduleType.ValueMember = "visit_type_id";
scheduleType.DisplayMember = "type";
travelingMode.DataSource = travelModeDataSource();
travelingMode.ValueMember = "id";
travelingMode.DisplayMember = "details";
accomodation.DataSource = accomodationDataSource();
accomodation.ValueMember = "id";
accomodation.DisplayMember = "details";
serviceEngCombo.DataSource = serviceEngDataSource();
serviceEngCombo.ValueMember = "staff_id";
serviceEngCombo.DisplayMember = "fullname";
eventResoCombo.DataSource = eventResourcesSource();
eventResoCombo.ValueMember = "resource_id";
eventResoCombo.DisplayMember = "name";
projectNameChange(evnt.EventProject.ProjectID);
engGrid.Columns.Add("staff_id", typeof(int));
engGrid.Columns.Add("fullname", typeof(string));
feedbackGrid.Columns.Add("staff_id", typeof(int));
feedbackGrid.Columns.Add("fullname", typeof(string));
feedbackGrid.Columns.Add("feedback", typeof(string));
feedbackGrid.Columns.Add("task", typeof(string));
feedbackGrid.Columns.Add("app", typeof(string));
feedbackGrid.Columns.Add("used", typeof(string));
serviceEngGrid.DataSource = engGrid;
clientFeedback.DataSource = feedbackGrid;
serviceEngFeed.DataSource = engGrid;
serviceEngFeed.ValueMember = "staff_id";
serviceEngFeed.DisplayMember = "fullname";
projectName.SelectedValue = evnt.EventProject.ProjectID;
eventClientName.SelectedValue = evnt.EventProject.ClientOfProject.ClientID;
eventsSch.SelectedValue = evnt.ScheduleId.ScheduleId;
scheduleType.SelectedValue = evnt.Type.EventTypeId;
todoList.Text = evnt.TodoList;
eventStartDate.Value = evnt.From;
eventStartTime.Value = evnt.From;
eventEndDate.Value = evnt.To;
eventEndTime.Value = evnt.To;
travelingMode.Text = evnt.TravelMode;
accomodation.Text = evnt.AccommodationMode;
meals.Text = evnt.Meals;
checkList.Text = evnt.Checklist;
overFeedback.Text = evnt.Feedback;
other.Text = evnt.Other;
ArrayList eng = evnt.ServEngineer;
foreach (var item in eng)
{
DataRow erow, frow;
EventTechnician et = (EventTechnician)item;
erow = engGrid.NewRow();
erow["staff_id"] = et.Technician.StaffId;
erow["fullname"] = et.Technician.FirstName + " " + et.Technician.LastName;
engGrid.Rows.Add(erow);
foreach (EventTask tsk in et.Task)
{
frow = feedbackGrid.NewRow();
frow["staff_id"] = et.Technician.StaffId;
frow["fullname"] = et.Technician.FirstName + " " + et.Technician.LastName;
frow["task"] = tsk.Task;
frow["feedback"] = tsk.Fb;
frow["app"] = tsk.AppTime;
frow["used"] = tsk.UsedTime;
try
{
feedbackGrid.Rows.Add(frow);
}
catch (Exception exc)
{
MessageBox.Show("" + exc);
}
}
}
ArrayList reso = evnt.ResoArray;
resoTbl.Columns.Add("resource_id", typeof(int));
resoTbl.Columns.Add("name", typeof(string));
resoTbl.Columns.Add("qty", typeof(int));
resoTbl.PrimaryKey = new DataColumn[] { resoTbl.Columns["resource_id"] };
foreach (var item in reso)
{
DataRow row;
Resource r = (Resource)item;
row = resoTbl.NewRow();
row["resource_id"] = r.ResourceId;
row["name"] = r.Name;
row["qty"] = r.TotalQty;
resoTbl.Rows.Add(row);
}
resoGrid.DataSource = resoTbl;
clientFeedback.Columns[0].Visible = false;
serviceEngGrid.Columns[0].Visible = false;
resoGrid.Columns[0].Visible = false;
}
private Event getEventRow(int proj_id, int sch_no, int event_id)
{
Event evnt = new Event();
try
{
MySqlDataReader reader = DBConnection.getData("select e.event_id, e.proj_id, e.visit_type_id, e.to_date_time, e.from_date_time, e.sch_no, e.feedback, e.Other, e.resource, e.check_list, e.travelling_mode, e.accommodation_mode, e.meals, p.client_id " +
" from event e, project p " +
" where e.proj_id = p.proj_id and e.event_id = " + event_id + " and e.proj_id = " + proj_id + " and e.sch_no = " + sch_no + ";");
if (reader.Read())
{
evnt.EventId = event_id;
evnt.EventProject = new Project(proj_id, reader.GetInt16("client_id"));
evnt.ScheduleId = new Schedule(sch_no);
evnt.Type = new EventType(reader.GetInt16("visit_type_id"));
evnt.From = reader.GetDateTime("from_date_time");
evnt.To = reader.GetDateTime("to_date_time");
evnt.Feedback = reader.GetString("feedback");
evnt.Other = reader.GetString("other");
evnt.Checklist = reader.GetString("check_list");
evnt.TravelMode = reader.GetString("travelling_mode");
evnt.AccommodationMode = reader.GetString("accommodation_mode");
evnt.Meals = reader.GetString("meals");
reader.Close();
string tasks = "";
reader = DBConnection.getData("select task from schedule_task where sch_no=" + sch_no + " and proj_id=" + proj_id + " order by sch_task_id");
while (reader.Read())
{
tasks += reader.GetString(0) + "/";
}
reader.Close();
evnt.TodoList = tasks;
MySqlDataReader reader1 = DBConnection.getData("select et.event_staff_id, s.staff_id, s.first_name, s.last_name " +
"from event_technicians et, staff s " +
"where (s.staff_id = et.staff_id) and et.event_id = " + event_id + " and et.proj_id = " + proj_id + " and et.sch_no = " + sch_no + ";");
ArrayList serEng = new ArrayList();
while (reader1.Read())
{
EventTechnician et = new EventTechnician();
et.Technician = new Staff(reader1.GetInt16("staff_id"), reader1.GetString("first_name"), reader1.GetString("last_name"));
MySqlDataReader r = DBConnection.getDataViaTmpConnection("select task, used_time, appointed_time, feedback from event_technician__task where event_tech_id=" + reader1.GetString(0));
while (r.Read())
{
et.Task.Add(new EventTask(r.GetString(0), r.GetString("feedback"), "" + r.GetTimeSpan(1).Hours + ":" + r.GetTimeSpan(1).Minutes, "" + r.GetTimeSpan(2).Hours + ":" + r.GetTimeSpan(2).Minutes));
}
r.Close();
DBConnection.closeTmpConnection();
serEng.Add(et);
}
evnt.ServEngineer = serEng;
reader1.Close();
ArrayList resoArray = new ArrayList();
MySqlDataReader reader3 = DBConnection.getData("select sr.resource_id, sr.qty, r.name from event_resources sr, resource r where (sr.event_id = " + event_id + " and sr.sch_no =" + sch_no + " and sr.proj_id = " + proj_id + ") and (sr.resource_id = r.resource_id);");
while (reader3.Read())
{
Resource reso = new Resource();
reso.ResourceId = int.Parse(reader3.GetString("resource_id"));
reso.Name = reader3.GetString("name");
reso.TotalQty = int.Parse(reader3.GetString("qty"));
resoArray.Add(reso);
}
evnt.ResoArray = resoArray;
reader3.Close();
}
else
{
evnt = null;
}
}
catch (Exception exc)
{
MessageBox.Show("Something went wrong!\n" + exc, "Update/View Event", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(evnt);
}
