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;
}
}
}
}