private void Loginbutton_Click(object sender, EventArgs e) { try { DataTable dt = null; if (!PWtextBox.Text.Equals("")) { dt = database.getTable("select * from login where staff_no ='" + staffIdtextBox.Text + "' and pw = '" + PWtextBox.Text + "';", "login"); } else { dt = database.getTable("select * from login where staff_no ='" + staffIdtextBox.Text + "' and isnull(pw)", "login"); } if (!dt.Rows.Count.Equals(0)) { userId = Convert.ToInt32(dt.Rows[0][0]); isclose = false; this.Close(); } else { MessageBox.Show("Wrong!"); } } catch (Exception ex) { MessageBox.Show("Couldn 't connet the database!"); } }
public void createHistory() { DataTable dt = database.getTable("select * from comment where userId = " + userId + " and( len( toiltaked )>9 or len( toiladded )>9) order by dateoflast asc", "comment"); double toil = 0.0; foreach (DataRow dr in dt.Rows) { try { if (Convert.ToDateTime(dr["toiltaked"]).ToString("HH:mm:ss") != "00:00:00") { toil -= Convert.ToDouble(Convert.ToDateTime(dr["toiltaked"]).Hour + "." + Convert.ToDateTime(dr["toiltaked"]).Minute * 10 / 60); historyList.Rows.Add(Convert.ToDateTime(dr["toiltaked"]).ToShortDateString(), "", Convert.ToDateTime(dr["toiltaked"]).Hour + "." + Convert.ToDateTime(dr["toiltaked"]).Minute * 10 / 60, toil.ToString("F1")); historyList.Rows[historyList.Rows.Count - 1].Cells[2].Style.ForeColor = Color.Red; } else if (Convert.ToDateTime(dr["toiladded"]).ToString("HH:mm:ss") != "00:00:00") { toil += Convert.ToDouble(Convert.ToDateTime(dr["toiladded"]).Hour + "." + Convert.ToDateTime(dr["toiladded"]).Minute * 10 / 60); historyList.Rows.Add("", Convert.ToDateTime(dr["toiladded"]).ToShortDateString(), Convert.ToDateTime(dr["toiladded"]).Hour + "." + Convert.ToDateTime(dr["toiladded"]).Minute * 10 / 60, toil.ToString("F1")); historyList.Rows[historyList.Rows.Count - 1].Cells[2].Style.ForeColor = Color.Green; } } catch (Exception ex) { } } }
private void applybutton_Click(object sender, EventArgs e) { LoadData database = new LoadData(); admin = new Admin(namelist); switch (applybutton.Text) { case "Add": database.runsqlcmd("insert into login(staff_no) values('" + staffnumbertextBox.Text + "');"); database.runsqlcmd("insert into staffstatus (staff_name, userId, staff_no, grade, unit, cc ) values('" + staffnametextBox.Text + "'," + database.getTable("select LAST(userId) from login", "login").Rows[0][0] + ",'" + staffnumbertextBox.Text + "','" + gradecomboBox.Text + "','" + unitcomboBox.Text + "','" + CCcomboBox.Text + "');"); admin.createNameList(); this.Close(); break; case "Moditfly": database.runsqlcmd("update staffstatus set staff_name = '" + staffnametextBox.Text + "', staff_no = '" + staffnumbertextBox.Text + "', " + "grade = '" + gradecomboBox.Text + "'," + "unit = '" + unitcomboBox.Text + "'," + "cc = '" + CCcomboBox.Text + "' " + "where staffId = " + staffId ); admin.createNameList(); this.Close(); break; } }
private void applyButton_Click(object sender, EventArgs e) { string ss = ""; foreach (string s in commentBox.Text.Split(new[] { "\n" }, StringSplitOptions.None)) { if (s.Length > 50) { int i; for (i = 50; i < s.Length; i = i + 50) { ss += s.Substring(i - 50, 50) + "\n"; } ss += s.Substring(i - 50, s.Length - (i - 50)); } else { ss += s + "\n"; } } commentBox.Text = ss; if (isnewcomment) { database.runsqlcmd("insert into comment (comment, dateoflast, userId) values ('" + ss + "','" + datetime + "','" + userId + "') ;"); dgvc.Tag = database.getTable("select * from comment where userId = " + userId.ToString() + " and dateoflast =CDate('" + datetime + "');", "comment").Rows[0][0]; } else { database.runsqlcmd("update comment set comment ='" + ss + "'" + " where commentId =" + commentId + ";"); } isapply = true; this.Close(); }
public Description(string datetime, int userId, string commentId = "", bool isnewcomment = false, DataGridViewCell dgvc = null) { InitializeComponent(); DataTable dt; LoadData database = new LoadData(); this.database = database; this.datetime = Convert.ToDateTime(datetime).ToString(); dt = database.getTable("select * from comment where userId = " + userId.ToString() + ";", "comment"); DataColumn[] dc = new DataColumn[] { dt.Columns[0] }; if (!isnewcomment) { commentBox.Text = database.getDateRowByKey(dc, new object[] { commentId }, dt)[1].ToString(); this.commentId = commentId; } this.isnewcomment = isnewcomment; this.userId = userId.ToString(); this.dgvc = dgvc; }
public void createNameList() { foreach (ListViewItem lvi in namelist.Items) { lvi.Remove(); } LoadData database = new LoadData(); DataTable dt = database.getTable("select * from staffstatus ", "staffstatus"); foreach (DataRow dr in dt.Rows) { try { namelist.Items.Add(new ListViewItem(new[] { dr[0].ToString(), dr["staff_name"].ToString(), dr["staff_no"].ToString(), dr["unit"].ToString(), dr["grade"].ToString(), dr["cc"].ToString() })); } catch (Exception ex) { } } }
public void updatestatus(timepicker tp, DateTime date) { try { LoadData database = new LoadData(); DataTable dt = database.getTable("select * from comment where commentId =" + commentId.ToString() + ";", "comment"); tp.start.Value = Convert.ToDateTime(dt.Rows[0][4]); tp.end.Value = Convert.ToDateTime(dt.Rows[0][5]); tp.otend.Value = Convert.ToDateTime(dt.Rows[0][7]); tp.otstart.Value = Convert.ToDateTime(dt.Rows[0][6]); tp.toiladded.Value = Convert.ToDateTime(dt.Rows[0][9]); tp.toiltaked.Value = Convert.ToDateTime(dt.Rows[0][8]); tp.isA.Checked = Convert.ToBoolean(dt.Rows[0][11]); tp.isN.Checked = Convert.ToBoolean(dt.Rows[0][12]); tp.isM.Checked = Convert.ToBoolean(dt.Rows[0][13]); tp.isECO.Checked = Convert.ToBoolean(dt.Rows[0][14]); tp.isDrive.Checked = Convert.ToBoolean(dt.Rows[0][15]); tp.isSPday.Checked = Convert.ToBoolean(dt.Rows[0][16]); tp.isStandby.Checked = Convert.ToBoolean(dt.Rows[0][17]); } catch (Exception ex) { tp.start.Value = date.AddHours(8.5); tp.end.Value = date.AddHours(18); tp.otend.Value = date; tp.otstart.Value = date; tp.toiladded.Value = date; tp.toiltaked.Value = date; tp.isA.Checked = false; tp.isN.Checked = false; tp.isM.Checked = false; tp.isECO.Checked = false; tp.isDrive.Checked = false; tp.isSPday.Checked = false; tp.isStandby.Checked = false; } }
private void Calendar_SelectionChanged(object sender, EventArgs e) { /*check the selcet status, siganl of muilti?*/ if (Calendar.SelectedCells.Count == 1) { duplicateThisDayToolStripMenuItem.Enabled = true; selcetCellcommentId = Convert.ToInt32(Calendar.SelectedCells[0].Tag); isNcheckBox.CheckedChanged -= new System.EventHandler(this.isNcheckBox_CheckedChanged); isMcheckBox.CheckedChanged -= new System.EventHandler(this.isMcheckBox_CheckedChanged); isAcheckBox.CheckedChanged -= new System.EventHandler(this.isAcheckBox_CheckedChanged); if (!(Calendar.SelectedCells[0].Tag == null)) { DutyStatus status = new DutyStatus(selcetCellcommentId); status.updatestatus(tp, Convert.ToDateTime(calendar.getDateByCaleandar(Calendar.SelectedCells[0].RowIndex, Calendar.SelectedCells[0].ColumnIndex))); modifybutton.Enabled = true; notificationlabel.Text = database.getTable("select * from comment where commentId = " + Calendar.SelectedCells[0].Tag, "comment").Rows[0]["daystatus"].ToString(); } else { DutyStatus status = new DutyStatus(); status.setByDefult(tp, Convert.ToDateTime(calendar.getDateByCaleandar(Calendar.SelectedCells[0].RowIndex, Calendar.SelectedCells[0].ColumnIndex))); modifybutton.Enabled = false; notificationlabel.Text = "No data"; } isNcheckBox.CheckedChanged += new System.EventHandler(this.isNcheckBox_CheckedChanged); isMcheckBox.CheckedChanged += new System.EventHandler(this.isMcheckBox_CheckedChanged); isAcheckBox.CheckedChanged += new System.EventHandler(this.isAcheckBox_CheckedChanged); otTimelabel.Text = (OTenddateTimePicker.Value - OTstartdateTimePicker.Value).Hours.ToString() + "." + (((OTenddateTimePicker.Value - OTstartdateTimePicker.Value).Minutes) * 10 / 60).ToString(); } if (Calendar.SelectedCells.Count > 1) { duplicateThisDayToolStripMenuItem.Enabled = false; } selectcells = Calendar.SelectedCells; }
public void createReport(int userId, initiCalendar calaendar) { Workbook wb = null; Worksheet ws = null; Range range = null; string path = "//10.100.50.113\\caelan\\Temp\\worksheet\\C83E_From.xls"; LoadData database = new LoadData(); wb = _Excel.Workbooks.Open(path); ws = (Worksheet)wb.Sheets[1]; range = ws.get_Range("A1", "AE200"); System.Data.DataTable staffDT = database.getTable("select * from staffstatus where userId = " + userId, "staffstsus"); ws.Cells[3][4] = staffDT.Rows[0]["staff_name"]; ws.Cells[19][4] = staffDT.Rows[0]["grade"]; ws.Cells[25][4] = staffDT.Rows[0]["unit"]; ws.Cells[32][4] = staffDT.Rows[0]["cc"]; ws.Cells[3][5] = staffDT.Rows[0]["staff_no"]; ws.Cells[4][196] = "S/N " + staffDT.Rows[0]["staff_name"]; ws.Cells[19][5] = intToMonth(calaendar.getcurrentMonth()) + calaendar.getcurrentYear(); int i = 9; HashSet <int> numbers; if (calaendar.getweekOfFirstDayOnMonth() == 0) { /*check the day of first, if your first day is sunday, you should start the week on row 35*/ numbers = new HashSet <int> { 35, 41, 45, 49, 53, 57, 61, 65, 71, 75, 79, 83, 87, 91, 95, 101, 105, 109, 113, 117, 121, 125, 131, 135, 139, 143, 147, 151, 155, 161, 165, 169, 173, 177, 181, 185 }; i = 35; } else { /*orther start time are using this patten*/ numbers = new HashSet <int> { 11, 15, 19, 23, 27, 31, 35, 41, 45, 49, 53, 57, 61, 65, 71, 75, 79, 83, 87, 91, 95, 101, 105, 109, 113, 117, 121, 125, 131, 135, 139, 143, 147, 151, 155, 161, 165, 169, 173, 177, 181, 185 }; } System.Data.DataTable dt = database.getTable("select * from comment where userId = " + userId + " order by dateoflast ASC ;", "comment"); System.Data.DataColumn[] dc = new System.Data.DataColumn[] { dt.Columns[0] }; int AL = Convert.ToInt32(database.getTable("select * from staffstatus where userId = " + userId, "staffstatus").Rows[0]["al_balance"]); try { foreach (System.Windows.Forms.DataGridViewRow dgvr in calaendar.getCalendar().Rows) { foreach (System.Windows.Forms.DataGridViewCell dgvc in dgvr.Cells) { /*check the date is or not now month, and find the day status is it normal, if yes, do some to excel, if not, yet*/ if (Convert.ToDateTime(calaendar.getDateByCaleandar(dgvc.RowIndex, dgvc.ColumnIndex)).Month == calaendar.getcurrentMonth()) { ws.Cells[2][i] = Convert.ToDateTime(calaendar.getDateByCaleandar(dgvc.RowIndex, dgvc.ColumnIndex)).Day.ToString(); if (dgvc.Tag == null) { } else { System.Data.DataRow dr = database.getDateRowByKey(dc, new object[] { dgvc.Tag }, dt); if (dr["daystatus"].Equals("Normal")) { string[] s = dr[1].ToString().Split(new char[] { }); int j = 0; foreach (string ss in s) { ws.Cells[32][i + j] = ss; j++; } ws.Cells[2][i] = Convert.ToDateTime(calaendar.getDateByCaleandar(dgvc.RowIndex, dgvc.ColumnIndex)).Day.ToString(); ws.Cells[3][i] = Convert.ToDateTime(dr[4]).ToString("HH:mm"); ws.Cells[4][i] = Convert.ToDateTime(dr[5]).ToString("HH:mm"); try { if (!((Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Hours.ToString() + "." + (((Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Minutes) * 10 / 60).ToString()).Equals("0.0")) { ws.Cells[6][i] = Convert.ToDateTime(dr["otend"]).ToString("HH:mm"); ws.Cells[5][i] = Convert.ToDateTime(dr["otstart"]).ToString("HH:mm"); if (Convert.ToBoolean(dr[14])) { ws.Cells[11][i] = ((Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Hours.ToString() + "." + (((Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Minutes) * 10 / 60).ToString()); } else { ws.Cells[16][i] = (Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Hours.ToString() + "." + (((Convert.ToDateTime(dr["otend"]) - Convert.ToDateTime(dr["otstart"])).Minutes) * 10 / 60).ToString(); } } } catch (Exception ex) { } if (Convert.ToBoolean(dr["isA"])) { ws.Cells[19][i] = "1"; } if (Convert.ToBoolean(dr["isN"])) { ws.Cells[21][i] = "1"; } // if (Convert.ToBoolean(dr["isM"])) if (Convert.ToBoolean(dr["isECO"])) { ws.Cells[10][i] = "1"; } if (Convert.ToBoolean(dr["isDrive"])) { ws.Cells[25][i] = "1"; } if (Convert.ToBoolean(dr["isSPday"])) { ws.Cells[23][i] = "1"; } if (Convert.ToBoolean(dr["isStandby"])) { ws.Cells[27][i] = "1"; } } else { if (dr["daystatus"].Equals("Annual Leave")) { AL++; ws.Cells[29][i] = -1; } ws.Cells[3][i] = getstatus(database.getDateRowByKey(dc, new object[] { dgvc.Tag }, dt)["daystatus"].ToString()); } } } for (i = i + 2; !numbers.Contains(i); i = i + 2) { ; } } } ws.Cells[29][10] = AL; } catch (Exception ex) { } wb.SaveAs("C:\\WS\\test.xls"); wb.Close(); }
public void createCalendar(int whitchmonth, int whitchyear, string userId) { currentMonth = whitchmonth; currentYear = whitchyear; for (int k = 0; k < 6; k++) { date[k] = new string[] { "", "", "", "", "", "", "" }; commentId[k] = new string[] { "", "", "", "", "", "", "" }; } DateTime dt = new DateTime(DateTime.Now.Year, whitchmonth, 1); int weekOfFirstDayOnMonth = (int)dt.DayOfWeek; int i = 1; this.weekOfFirstDayOnMonth = weekOfFirstDayOnMonth; /*check the first day is or not sunday, if not, do that*/ if (weekOfFirstDayOnMonth != 0) { for (int j = 0; j < weekOfFirstDayOnMonth; j++) { calendar.Rows[0].Cells[j].Style.BackColor = Color.White; if (dt.AddDays(-j).Date < DateTime.Now.Date) { calendar.Rows[0].Cells[j].Style.ForeColor = Color.Blue; } if (dt.AddDays(-j).Date > DateTime.Now.Date) { calendar.Rows[0].Cells[j].Style.ForeColor = Color.Gray; } calendar.Rows[0].Cells[j].Value = dt.AddDays(-(weekOfFirstDayOnMonth - j)).Day; calendar.Rows[0].Cells[j].Tag = null; date[0][j] = dt.AddDays(-(weekOfFirstDayOnMonth - j)).Day + "/" + dt.AddDays(-(weekOfFirstDayOnMonth - j)).Month + "/" + dt.AddDays(-(weekOfFirstDayOnMonth - j)).Year; } } foreach (DataGridViewRow dgv in calendar.Rows) { /*loop the week form sunday to statraday to configure the date and calendar*/ while (weekOfFirstDayOnMonth < 7) { dgv.Cells[weekOfFirstDayOnMonth].Style.Font = new Font("Consolas", 12, FontStyle.Regular); dgv.Cells[weekOfFirstDayOnMonth].Style.ForeColor = Color.Black; dgv.Cells[weekOfFirstDayOnMonth].Style.BackColor = Color.White; if (dt.AddDays(i - 1).Date == DateTime.Now.Date && dt.AddDays(i - 1).Month == whitchmonth) { dgv.Cells[weekOfFirstDayOnMonth].Style.Font = new Font("Consolas", 16, FontStyle.Bold); } if (dt.AddDays(i - 1).Date > DateTime.Now.Date & dt.AddDays(i - 1).Month == DateTime.Now.Month) { dgv.Cells[weekOfFirstDayOnMonth].Style.ForeColor = Color.IndianRed; } if (dt.AddDays(i - 1).Month < DateTime.Now.Month & dt.AddDays(i - 1).Date < DateTime.Now.Date) { dgv.Cells[weekOfFirstDayOnMonth].Style.ForeColor = Color.Blue; } if (dt.AddDays(i - 1).Month > DateTime.Now.Month | dt.AddDays(i - 1).Year > DateTime.Now.Year) { dgv.Cells[weekOfFirstDayOnMonth].Style.ForeColor = Color.Gray; } dgv.Cells[weekOfFirstDayOnMonth].Value = dt.AddDays(i - 1).Day; dgv.Cells[weekOfFirstDayOnMonth].Tag = null; date[dgv.Index][weekOfFirstDayOnMonth] = dt.AddDays(i - 1).Day + "/" + dt.AddDays(i - 1).Month + "/" + dt.AddDays(i - 1).Year; weekOfFirstDayOnMonth++; i++; } weekOfFirstDayOnMonth = 0; } weekOfFirstDayOnMonth = (int)dt.DayOfWeek; LoadData database = new LoadData(); foreach (DataRow dr in database.getTable("select * from comment where userId = " + userId, "comment").Rows) { if (Convert.ToDateTime(dr[2]).Month.Equals(whitchmonth)) { /*mark the tag to be the id, for key using only*/ calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Tag = dr[0]; switch (dr["daystatus"].ToString()) { case "Normal": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.Green; break; case "Annual Leave": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.Red; break; case "Sick Leave": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.RoyalBlue; break; case "Pubilc Holiday": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.YellowGreen; break; case "Day Off": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.DimGray; break; case "Rest Day": calendar.Rows[(Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) / 7].Cells[((Convert.ToDateTime(dr[2]).Day + weekOfFirstDayOnMonth - 1) % 7)].Style.BackColor = Color.Maroon; break; } } } }