using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Globalization;



namespace App.EmployeeMenu

{

 public partial class EmployeeScheduleForm : Form

 {

 private DateTime currentDateTime;

 private DataTable department;

 public EmployeeScheduleForm()

 {

 InitializeComponent();

 currentDateTime = DateTime.Now;



 // get departmentlist

 getDepartment();

 departmentSelection.SelectedIndex = 0;



 // select current month

 populateMonthSelection();

 monthSelection.SelectedValue = currentDateTime.Month;



 // create year list and select current year;

 populateYearSelection();

 yearSelection.SelectedIndex = 10;

 }



 private void getDepartment()

 {

 department = ApplicationSystem.sqlHelper.getDataTable("SELECT * FROM department ORDER BY name ASC");

 departmentSelection.DataSource = department;

 departmentSelection.DisplayMember = "name";

 departmentSelection.ValueMember = "id";

 }



 private void populateMonthSelection()

 {

 Dictionary<string, int> months = new Dictionary<string, int>();

 months.Add("January", 1);

 months.Add("Frebruary", 2);

 months.Add("March", 3);

 months.Add("April", 4);

 months.Add("May", 5);

 months.Add("June", 6);

 months.Add("July", 7);

 months.Add("August", 8);

 months.Add("September", 9);

 months.Add("October", 10);

 months.Add("November", 11);

 months.Add("December", 12);



 monthSelection.DataSource = new BindingSource(months, null);

 monthSelection.DisplayMember = "Key";

 monthSelection.ValueMember = "Value";

 }



 private void populateYearSelection()

 {

 int currentYear = currentDateTime.Year;

 int lestTenYear = currentYear - 10;

 int moreTenYear = currentYear + 10;

 for (int i = lestTenYear; i < moreTenYear; i++)

 {

 yearSelection.Items.Add(i);

 }

 }



 private void EmployeeScheduleForm_KeyDown(object sender, KeyEventArgs e)

 {

 if (e.KeyCode == Keys.Escape)

 {

 this.Close();

 }

 }



 private void EmployeeScheduleForm_Shown(object sender, EventArgs e)

 {

 departmentSelection.SelectedIndexChanged += departmentSelection_SelectedIndexChanged;

 monthSelection.SelectedIndexChanged += monthSelection_SelectedIndexChanged;

 yearSelection.SelectedIndexChanged += yearSelection_SelectedIndexChanged;

 }



 private int selectedDepartment, selectedMonth, selectedYear;



 void yearSelection_SelectedIndexChanged(object sender, EventArgs e)

 {

 selectedYear = Convert.ToInt16(yearSelection.SelectedItem.ToString());

 loadEmployee(); 

 }



 void monthSelection_SelectedIndexChanged(object sender, EventArgs e)

 {

 selectedMonth = Convert.ToInt16(monthSelection.SelectedValue);

 loadEmployee();

 }



 void departmentSelection_SelectedIndexChanged(object sender, EventArgs e)

 {

 selectedDepartment = Convert.ToInt16(departmentSelection.SelectedValue);

 loadEmployee();

 }



 private void EmployeeScheduleForm_Load(object sender, EventArgs e)

 {

 selectedDepartment = Convert.ToInt16(departmentSelection.SelectedValue);

 selectedMonth = Convert.ToInt16(monthSelection.SelectedValue);

 selectedYear = Convert.ToInt16(yearSelection.SelectedItem.ToString());

 loadEmployee();

 }



 private void loadEmployee()

 {

 string employeeSql = string.Format("SELECT id, name FROM employee WHERE active = 1 AND department_id = '{0}'", selectedDepartment);

 DataTable employeeTable = ApplicationSystem.sqlHelper.getDataTable(employeeSql);

 int days = DateTime.DaysInMonth(selectedYear, selectedMonth);

 for (int i = 1; i <= days; i++)

 {

 DateTime date = new DateTime(selectedYear, selectedMonth, i);

 DataColumn dateColumn = new DataColumn(date.DayOfWeek.ToString() + " " + date.ToShortDateString(), typeof(string));

 employeeTable.Columns.Add(dateColumn);

 }

 employeeDataGridView.DataSource = employeeTable;

 makeGridNotSortableAndMarkSunday();

 loadScheduleFromDatabase();

 }



 private void loadScheduleFromDatabase()

 {

 foreach (DataGridViewRow scheduleRow in employeeDataGridView.Rows)

 {

 string employeeID = scheduleRow.Cells["id"].Value.ToString();

 string loadEmployeeSchedule = string.Format("SELECT * FROM schedule WHERE employee_id = {0} AND s_month = '{1}' AND s_year = '{2}';", employeeID, selectedMonth, selectedYear);

 DataTable employeeTable = ApplicationSystem.sqlHelper.getDataTable(loadEmployeeSchedule);

 foreach (DataRow rowData in employeeTable.Rows)

 {

 int day = Convert.ToInt16(rowData["s_day"]);

 int workState = Convert.ToInt16(rowData["workstate_id"]);

 Color cellColor = scheduleRow.Cells[day + 2].Style.BackColor;

 switch (workState)

 {

 case 2:

 cellColor = Color.FromArgb(231, 76, 60);

 break;



 case 3:

 cellColor = Color.FromArgb(41, 128, 185);

 break;



 case 4:

 cellColor = Color.FromArgb(39, 174, 96);

 break;



 default:

 cellColor = Color.Empty;

 break;

 }

 scheduleRow.Cells[day].Style.BackColor = cellColor;

 scheduleRow.Cells[day].Style.SelectionBackColor = cellColor;

 }

 }

 }



 private void makeGridNotSortableAndMarkSunday()

 {

 employeeDataGridView.EnableHeadersVisualStyles = false;

 foreach (DataGridViewColumn column in employeeDataGridView.Columns)

 {

 string columnName = column.Name;

 DataGridViewColumn col = employeeDataGridView.Columns[columnName];

 col.SortMode = DataGridViewColumnSortMode.NotSortable;



 // ganti background minggu jadi merah

 bool val = System.Text.RegularExpressions.Regex.IsMatch(columnName, @"\d");

 if (val)

 {

 col.Width = 70;

 }

 if (columnName.StartsWith("Sunday"))

 {

 col.HeaderCell.Style.BackColor = Color.FromArgb(231, 76, 60);

 }

 }

 }



 private void employeeDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)

 {

 if (e.RowIndex < 0)

 {

 return;

 }



 if (e.ColumnIndex > 1)

 {

 DataGridViewCell selectedCell = employeeDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex];

 DataGridViewCellStyle selectedCellStyle = selectedCell.Style;



 if (selectedCellStyle.BackColor == Color.Empty)

 {

 // DAY OFF

 selectedCellStyle.BackColor = Color.FromArgb(231, 76, 60);

 selectedCellStyle.SelectionBackColor = Color.FromArgb(231, 76, 60);

 }

 else if (selectedCellStyle.BackColor == Color.FromArgb(231, 76, 60))

 {

 // ANNUAL LEAVE

 selectedCellStyle.BackColor = Color.FromArgb(41, 128, 185);

 selectedCellStyle.SelectionBackColor = Color.FromArgb(41, 128, 185);

 }

 else if (selectedCellStyle.BackColor == Color.FromArgb(41, 128, 185))

 {

 // PUBLIC HOLIDAY

 selectedCellStyle.BackColor = Color.FromArgb(39, 174, 96);

 selectedCellStyle.SelectionBackColor = Color.FromArgb(39, 174, 96);

 }

 else if (selectedCellStyle.BackColor == Color.FromArgb(39, 174, 96))

 {

 // WORK

 selectedCellStyle.BackColor = Color.Empty;

 selectedCellStyle.SelectionBackColor = Color.Empty;

 }



 }

 }



 private void updateWorkScheduleToDatabase()

 {

 List<Dictionary<string, string>> schedulesList = new List<Dictionary<string, string>>();

 foreach (DataGridViewRow scheduleRow in employeeDataGridView.Rows)

 {

 string employeeID = scheduleRow.Cells["id"].Value.ToString();

 for (int i = 2; i < scheduleRow.Cells.Count; i++)

 {

 Dictionary<string, string> scheduleDic = new Dictionary<string, string>();

 scheduleDic.Add("employee_id", employeeID);

 scheduleDic.Add("s_day", i.ToString());

 scheduleDic.Add("s_month", selectedMonth.ToString());

 scheduleDic.Add("s_year", selectedYear.ToString());

 scheduleDic.Add("create_date", DateTime.Now.ToString());

 scheduleDic.Add("create_user_id", ApplicationSystem.userProperties.id.ToString());



 string state = "";



 Color cellBackColor = scheduleRow.Cells[i].Style.BackColor;

 if (cellBackColor == Color.Empty)

 {

 state = "1"; // WORK

 }

 else if (cellBackColor == Color.FromArgb(231, 76, 60))

 {

 state = "2"; // DAY OFF

 }

 else if (cellBackColor == Color.FromArgb(41, 128, 185))

 {

 state = "3"; // ANNUAL LEAVE

 }

 else if (cellBackColor == Color.FromArgb(39, 174, 96))

 {

 state = "4";

 }

 scheduleDic.Add("workstate_id", state);

 schedulesList.Add(scheduleDic);

 }

 }



 foreach (Dictionary<string, string> scheduleDic in schedulesList)

 {

 string employeeId, workstate, day, month, year;

 string dbid, dbEmployeeId, dbWorksate, dbDay, dbMonth, dbYear;

 scheduleDic.TryGetValue("employee_id", out employeeId);

 scheduleDic.TryGetValue("workstate_id", out workstate);

 scheduleDic.TryGetValue("s_day", out day);

 scheduleDic.TryGetValue("s_month", out month);

 scheduleDic.TryGetValue("s_year", out year);



 string databaseSchedule = string.Format("SELECT * FROM schedule where employee_id = '{0}' AND s_day = '{1}' AND s_month = '{2}' AND s_year = '{3}';", employeeId, day, month, year);

 DataTable employeeSchedule = ApplicationSystem.sqlHelper.getDataTable(databaseSchedule);

 if (employeeSchedule.Rows.Count > 0)

 {

 dbid = employeeSchedule.Rows[0]["id"].ToString();

 dbEmployeeId = employeeSchedule.Rows[0]["employee_id"].ToString();

 dbWorksate = employeeSchedule.Rows[0]["workstate_id"].ToString();

 dbDay = employeeSchedule.Rows[0]["s_day"].ToString();

 dbMonth = employeeSchedule.Rows[0]["s_month"].ToString();

 dbYear = employeeSchedule.Rows[0]["s_year"].ToString();



 if (workstate.Equals("1"))

 {

 ApplicationSystem.sqlHelper.deleteData("schedule", "id = " + dbid);

 }

 else

 {

 ApplicationSystem.sqlHelper.updateData("schedule", scheduleDic, "id = " + dbid);

 }

 }

 else

 {

 if (!workstate.Equals("1"))

 {

 ApplicationSystem.sqlHelper.insertData("schedule", scheduleDic);

 }

 }

 }

 }



 private void updateButton_Click(object sender, EventArgs e)

 {

 updateWorkScheduleToDatabase();

 }

 }

}

mau nanya gan..
ini kenapa yah sewaktu gw update ke database lama amat yah?
kayak not responding gitu.. appnya berjalan lancar tapi prosesnya lama...
apakah ada cara yg lebih efisien dari ini?

kira2 hasilnya nanti seperti ini gan