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 MainPage()
{
InitializeComponent();
timepicker tp = new timepicker();
tp.otend = OTenddateTimePicker;
tp.otstart = OTstartdateTimePicker;
tp.start = startdateTimePicker;
tp.end = enddateTimePicker;
tp.toiladded = toiladdeddateTimePicker;
tp.toiltaked = toiltakeddateTimePicker;
tp.isA = isAcheckBox;
tp.isN = isNcheckBox;
tp.isM = isMcheckBox;
tp.isECO = isECOcheckBox;
tp.isDrive = isDrivecheckBox;
tp.otTimes = otTimelabel;
tp.isSPday = isSPdaycheckBox;
tp.isStandby = isStandbycheckBox;
this.tp = tp;
database = new LoadData();
LoginPage lp = new LoginPage(database);
lp.ShowDialog();
if (lp.isclosed())
{
this.Close();
}
userId = lp.getUserId();
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows.Add(Calendar.Rows);
Calendar.Rows[0].Height = 40;
Calendar.Rows[1].Height = 40;
Calendar.Rows[2].Height = 40;
Calendar.Rows[3].Height = 40;
Calendar.Rows[4].Height = 40;
Calendar.Rows[5].Height = 40;
Calendar.Rows[0].Cells[0].Selected = false;
foreach (DataGridViewColumn dgvc in Calendar.Columns)
{
dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
}
foreach (DataGridViewColumn dgvc in historyList.Columns)
{
dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
}
MonthcomboBox.SelectedItem = MonthcomboBox.Items[DateTime.Now.Month - 1];
this.MonthcomboBox.SelectedIndexChanged += new System.EventHandler(this.MonthcomboBox_SelectedIndexChanged);
for (int i = 2000; i <= DateTime.Now.Year; i++)
{
yearcomboBox.Items.Add(i);
}
yearcomboBox.SelectedItem = DateTime.Now.Year;
initiCalendar calendar = new initiCalendar(Calendar);
calendar.createCalendar(DateTime.Now.Month, DateTime.Now.Year, userId.ToString());
HistoryList history = new HistoryList(userId, historyList);
history.createHistory();
Admin admin = new Admin(namelistView);
admin.createNameList();
this.admin = admin;
this.calendar = calendar;
this.tabControl1.Controls.Remove(this.tabPage2);
this.tabControl1.Controls.Remove(this.tabPage3);
this.tabControl1.Controls.Remove(this.Toilexport);
}
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;
}
}
}
}
public LoginPage(LoadData database)
{
InitializeComponent();
this.database = database;
}