Ejemplo n.º 1
0
        void emptab()
        {
            var    connection = SqlConnectionManager.GetConnection();
            string query      = "SELECT * FROM [dbo].[tblEmployee] WHERE [EId]='" + txtEid.Text + "'";
            var    cmd        = SqlConnectionManager.GetCommand(query, connection);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    btnUpdate.Text = "Update";
                    while (reader.Read())
                    {
                        txtName.Text        = reader.GetString(1);
                        txtDepartment.Text  = reader.GetString(2);
                        txtDesignation.Text = reader.GetString(3);
                        DateTime dt1 = Convert.ToDateTime(reader["EJoiningDate"].ToString());
                        txtJoindate.Text = String.Format("{0:yyyy-MM-dd}", dt1);
                        txtGender.Text   = reader.GetString(6);
                        DateTime dt2 = Convert.ToDateTime(reader["EDob"].ToString());
                        txtDob.Text     = String.Format("{0:yyyy-MM-dd}", dt2);
                        txtAddress.Text = reader.GetString(8);
                        txtPhoneno.Text = reader.GetString(9);
                        txtEmail.Text   = reader.GetString(10);
                    }
                }
                else
                {
                    btnUpdate.Text = "Add";
                    // btnClear_Click(new object(), new EventArgs());
                }
            }
        }
Ejemplo n.º 2
0
        void paytab()
        {
            int    month      = DateTime.Now.Month;
            int    year       = DateTime.Now.Year;
            var    connection = SqlConnectionManager.GetConnection();
            string query      = "SELECT [BasicPayPD],[EAccno] FROM [dbo].[tblPayroll] WHERE [EId]='" + txtEid.Text + "' AND [Month]='" + month + "' AND [Year]='" + year + "'";
            var    cmd        = SqlConnectionManager.GetCommand(query, connection);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    btnUpdate.Text = "Update";
                    while (reader.Read())
                    {
                        txtAn.Text = reader["EAccno"].ToString();
                        txtBp.Text = reader["BasicPayPD"].ToString();
                    }
                }
                else
                {
                    btnUpdate.Text = "Add";
                    // btnClear_Click(new object(), new EventArgs());
                }
            }
        }
        protected void btnTimein_Click(object sender, EventArgs e)
        {
            var    connection = SqlConnectionManager.GetConnection();
            string eid        = ((string)Session["id"]);
            string intime     = DateTime.Now.ToString("HH:mm");
            string adate      = DateTime.Today.ToString("MM/dd/yyyy");
            string month      = DateTime.Now.Month.ToString();
            string year       = DateTime.Now.Year.ToString();
            string query      = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
            var    cmd        = SqlConnectionManager.GetCommand(query, connection);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    string it = "10:00";
                    while (reader.Read())
                    {
                        it = reader["Intime"].ToString();
                    }
                    if (it == "10:00")
                    {
                        lblError.Visible = false;
                        string         query2  = "UPDATE tblAttendance SET Intime='" + intime + "', Outtime='19:30', Status='present' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.InsertCommand = new SqlCommand(query2, connection);
                        adapter.InsertCommand.ExecuteNonQuery();
                        Response.Write("<script>alert('In-Time Registered Successfully.');</script>");
                    }
                    else
                    {
                        lblError.Visible = true;
                        lblError.Text    = "In-Time already registered!";
                    }
                }
                else
                {
                    string query2 = "SELECT * FROM tblEmployee";
                    var    cmd2   = SqlConnectionManager.GetCommand(query2, connection);
                    using (SqlDataReader reader2 = cmd2.ExecuteReader())
                    {
                        if (reader2.HasRows)
                        {
                            SqlDataAdapter sa = new SqlDataAdapter();
                            while (reader2.Read())
                            {
                                int    empid       = reader2.GetInt32(0);
                                string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
                                sa.InsertCommand = new SqlCommand(insertQuery, connection);
                                sa.InsertCommand.ExecuteNonQuery();
                            }
                        }
                    }
                    Response.Write("<script>alert('Unable to register In-time, try again.');window.location = 'eAttendance.aspx';</script>");
                }
            }
        }
        protected void btnTimeout_Click(object sender, EventArgs e)
        {
            var    connection = SqlConnectionManager.GetConnection();
            string eid        = ((string)Session["id"]);
            string outtime    = DateTime.Now.ToString("HH:mm");
            string adate      = DateTime.Today.ToString("MM/dd/yyyy");

            string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
            var    cmd   = SqlConnectionManager.GetCommand(query, connection);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    string it = "10:00";
                    string ot = "19:30";
                    while (reader.Read())
                    {
                        it = reader["Intime"].ToString();
                        ot = reader["Outtime"].ToString();
                    }
                    if (it != "10:00")
                    {
                        if (ot == "19:30")
                        {
                            lblError.Visible = false;
                            string         query2  = "UPDATE tblAttendance SET Outtime='" + outtime + "' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
                            SqlDataAdapter adapter = new SqlDataAdapter();
                            adapter.UpdateCommand = new SqlCommand(query2, connection);
                            adapter.UpdateCommand.ExecuteNonQuery();
                            Response.Write("<script>alert('Out-Time Registered Successfully.');</script>");
                        }
                        else
                        {
                            lblError.Visible = true;
                            lblError.Text    = "Out-Time already registered!";
                        }
                    }
                    else
                    {
                        lblError.Visible = true;
                        lblError.Text    = "In-Time not registered, register In-Time first!";
                    }
                }
            }
        }
Ejemplo n.º 5
0
        public string getDepartmentData()
        {
            string data       = "";
            var    connection = SqlConnectionManager.GetConnection();
            string query      = "SELECT [EDepartment],COUNT(*) as count FROM [dbo].[tblEmployee] GROUP BY [EDepartment] ORDER BY count DESC";
            var    cmd        = SqlConnectionManager.GetCommand(query, connection);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        string deptname      = reader.GetString(0);
                        int    employeetotal = reader.GetInt32(1);
                        data += "<tr><td>" + deptname + "</td><td>" + employeetotal + "</td></tr>";
                    }
                }
            }
            return(data);
        }
Ejemplo n.º 6
0
        void attendanceData()
        {
            var connection = SqlConnectionManager.GetConnection();

            connection.Open();
            string adate = DateTime.Today.ToString("MM/dd/yyyy");
            List <AttendanceDao> attendanceList = new List <AttendanceDao>();
            string     query = "SELECT tblAttendance.*,tblEmployee.EName FROM tblAttendance INNER JOIN tblEmployee ON tblAttendance.EId=tblEmployee.EId WHERE tblAttendance.[Adate]='" + adate + "'";
            SqlCommand cmd   = new SqlCommand();

            cmd.CommandText = query;
            cmd.Connection  = connection;
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    AttendanceDao attendanceData = new AttendanceDao();
                    attendanceData.AId     = reader.GetInt32(0);
                    attendanceData.EId     = reader.GetInt32(1);
                    attendanceData.Intime  = reader.IsDBNull(2) ? "" : reader.GetString(2);
                    attendanceData.Outtime = reader.IsDBNull(3) ? "" : reader.GetString(3);
                    if (attendanceData.Intime == "10:00")
                    {
                        attendanceData.Intime = "not available";
                    }

                    if (attendanceData.Outtime == "19:30")
                    {
                        attendanceData.Outtime = "not available";
                    }
                    int wh = 0, wm = 0, ithr, othr, itmin, otmin;
                    if (attendanceData.Intime != "10:00" && attendanceData.Outtime != "19:30")
                    {
                        Int32.TryParse(attendanceData.Intime.Substring(0, 2), out ithr);
                        Int32.TryParse(attendanceData.Outtime.Substring(0, 2), out othr);
                        Int32.TryParse(attendanceData.Intime.Substring(3), out itmin);
                        Int32.TryParse(attendanceData.Outtime.Substring(3), out otmin);
                        wh = othr - ithr;
                        wm = otmin - itmin;
                        wm = Math.Abs(wm);
                    }
                    attendanceData.EWorkinghours = wh.ToString() + "hrs " + wm.ToString() + "mins";
                    attendanceData.Adate         = reader.GetDateTime(4).ToString("dd/MM/yyyy");
                    attendanceData.Status        = reader.GetString(5);
                    attendanceData.Month         = reader.GetInt32(6);
                    attendanceData.Year          = reader.GetInt32(7);
                    attendanceData.EName         = reader.GetString(8);
                    attendanceList.Add(attendanceData);
                }
            }
            else
            {
                string query2 = "SELECT * FROM tblEmployee";
                var    cmd2   = SqlConnectionManager.GetCommand(query2, connection);
                using (SqlDataReader reader2 = cmd2.ExecuteReader())
                {
                    if (reader2.HasRows)
                    {
                        string         month = DateTime.Now.Month.ToString();
                        string         year  = DateTime.Now.Year.ToString();
                        SqlDataAdapter sa    = new SqlDataAdapter();
                        while (reader2.Read())
                        {
                            int    empid       = reader2.GetInt32(0);
                            string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
                            sa.InsertCommand = new SqlCommand(insertQuery, connection);
                            sa.InsertCommand.ExecuteNonQuery();
                        }
                    }
                }
                Response.Write("<script>window.location = 'frmAttendance.aspx';</script>");
            }

            if (attendanceList.ToList().Count > 0)
            {
                gvEmployee.DataSource = attendanceList;
                gvEmployee.DataBind();
            }
            else
            {
                AttendanceDao attendanceData = new AttendanceDao();
                attendanceData.EName = "dummy";
                attendanceList.Add(attendanceData);
                gvEmployee.DataSource = attendanceList;
                gvEmployee.DataBind();
                for (int i = 0; i < gvEmployee.Rows.Count; i++)
                {
                    gvEmployee.Rows[i].Cells.Clear();
                    gvEmployee.Rows[i].Visible = false;
                }
                gvEmployee.Rows[0].Visible        = true;
                gvEmployee.BottomPagerRow.Visible = false;
                gvEmployee.Rows[0].Cells.Add(new TableCell());
                gvEmployee.Rows[0].Cells[0].ColumnSpan      = gvEmployee.Columns.Count;
                gvEmployee.Rows[0].Cells[0].Text            = "No Data Found!";
                gvEmployee.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
            }
        }