Esempio n. 1
0
        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();
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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;
                    }
                }
            }
        }
Esempio n. 4
0
 public LoginPage(LoadData database)
 {
     InitializeComponent();
     this.database = database;
 }