Esempio n. 1
0
        public SalaryDetails GetMonthlySalaryByEmpId(string empid, string month, string year, string numberOfDays)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
            string salaryQuery = "select * from monthlySalary where emp_id = " + empid + " and month = '" + year + "/" + month + "/"+numberOfDays+" 00:00:00.000'";
            SqlCommand cmd = new SqlCommand(salaryQuery);
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "monthlySalary");
            List<SalaryDetails> salaryArtifactList = new List<SalaryDetails>();

            SalaryDetails salary = new SalaryDetails();
            AttendanceArtifact selectedMonth = new AttendanceArtifact();
            foreach (DataRow dr in ds.Tables["monthlySalary"].Rows)
            {
                salary.Id = int.Parse(empid);
                salary.MonthlySalary = float.Parse(dr["monthlySalary"].ToString());
                salary.Allowance = float.Parse(dr["allowance"].ToString());
                salary.Insurance = float.Parse(dr["insurance"].ToString());
                salary.NetSalary = float.Parse(dr["netSalary"].ToString());

                salaryArtifactList.Add(salary);

            }
            return salary;
        }
        public bool EditEmployee(EmployeeArtifact obj)
        {
            string query = "update employee set lname ='" + obj.LastName + "', middlename='" + obj.MiddleName + "' ,designation='" + obj.Designation
                + "',address='" + obj.Address + "',contactdetails = '" + obj.ContactDetails
                + "',active = '" + obj.Active + "', deleted= '" + obj.Deleted + "', dateofbirth = '" + obj.DateOfBirth + "',qualification = '"+obj.Qualification+"' where id = " + obj.Id;
            DbConnectionManager dbConnectionManager = new DbConnectionManager();
            SqlConnection connection = dbConnectionManager.connectToDb();

            SqlCommand command = new SqlCommand(query);
            command.Connection = connection;
            int result = command.ExecuteNonQuery();
            dbConnectionManager.diconnectFromDb(connection);

            if (result > 0)
            {

                return true;

            }
            else
            {
                return false;

            }
        }
Esempio n. 3
0
        private void change_btn_Click(object sender, EventArgs e)
        {
            if (newpassword_txt.Text == confirmpassword_txt.Text)
            {
                 checklogin login = new checklogin();
                bool isSuccessful = login.CheckLoginCredential(UserUtils.USERNAME.ToString(), oldpassword_txt.Text);
                if (isSuccessful)
                {
                    string query = "update login set password ='******' where username = '******' ";
                     DbConnectionManager dbConnectionManager= new DbConnectionManager ();
                    SqlConnection connection=dbConnectionManager.connectToDb();

                    SqlCommand command = new SqlCommand(query);
                    command.Connection = connection;
                    int result = command.ExecuteNonQuery();
                    if (result > 0)
                    {
                        MessageBox.Show("Password Successfully Changed");
                    }
                    else
                    {
                        MessageBox.Show("Unable to change password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            dbConnectionManager.diconnectFromDb(connection);

                }
            }
            else
            {
                MessageBox.Show("Password do not match", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            }
        }
Esempio n. 4
0
        private void AddAttendanceDetails(string empid)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();

            string     query1 = "select * from attendance where attendance_date = '" + dateTimePicker1.Value.Date + "'";
            SqlCommand cmd1   = new SqlCommand(query1);

            cmd1.Connection = conn;
            SqlDataAdapter da1 = new SqlDataAdapter();

            da1.SelectCommand = cmd1;
            DataSet ds1 = new DataSet();

            da1.Fill(ds1, "attendance");
            if (ds1.Tables["attendance"].Rows.Count != 0)
            {
                foreach (DataRow dr in ds1.Tables["attendance"].Rows)
                {
                    if (empid == (dr["employee_id"].ToString()))
                    {
                        Notice.Text = "Attendance Already Marked";
                    }
                    else
                    {
                        if (attendanceMarked(empid, conn) == true)
                        {
                            Notice.Text = "Attendance Marked Successfully";
                        }
                        else
                        {
                            Notice.Text = "Error! Try Again!";
                        }
                    }
                }
            }
            else
            {
                if (attendanceMarked(empid, conn) == true)
                {
                    Notice.Text = "Attendance Marked Successfully";
                }
                else
                {
                    Notice.Text = "Error! Try Again!";
                }
            }
            connect.diconnectFromDb(conn);
        }
        public string GetAttendanceCountByEmpID(string empid, string month, string year, string numberOfDays)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();
            string     attendanceQuery  = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' and present = 'True'";
            SqlCommand cmd1             = new SqlCommand(attendanceQuery);

            cmd1.Connection = conn;
            SqlDataAdapter da1 = new SqlDataAdapter();

            da1.SelectCommand = cmd1;
            DataSet ds1 = new DataSet();

            da1.Fill(ds1, "attendance");
            return(ds1.Tables["attendance"].Rows.Count.ToString());
        }
 private void delete_button_Click(object sender, EventArgs e)
 {
     DbConnectionManager connect = new DbConnectionManager();
     SqlConnection conn = connect.connectToDb();
     string query = "delete from employee where id = " + empid_txtbox.Text;
     SqlCommand cmd = new SqlCommand(query);
     cmd.Connection = conn;
     int result = cmd.ExecuteNonQuery();
     if (result > 0)
     {
         MessageBox.Show("Successfully Deleted");
     }
     else
     {
         MessageBox.Show("Error");
     }
 }
Esempio n. 7
0
        private void add_button_Click(object sender, EventArgs e)
        {
            bool workstate;

            if (active.Checked == true)
            {
                workstate = true;
            }
            else
            {
                workstate = false;
            }
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();
            string     query            = "insert into employee(fname,lname,middlename,designation,address,contactdetails,active,deleted,dateofbirth,qualification) values ('" + fname_txtbox.Text + "','" + lname_txtbox.Text + "','" + mname_txtbox.Text + "','" + designation_txtbox.Text + "','" + address_txtbox.Text + "','" + contact_txtbox.Text + "','" + workstate + "','False','" + dob_pick.Value.Date + "','" + qualification_txtbox.Text + "')";
            SqlCommand command          = new SqlCommand(query);

            command.Connection = conn;
            int result = command.ExecuteNonQuery();

            if (result > 0)
            {
                string selectid = "select max(id) as id from employee";
                command.CommandText = selectid;
                command.Connection  = conn;
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = command;
                DataSet ds = new DataSet();
                da.Fill(ds, "employee_id");
                DataRow    dr          = ds.Tables["employee_id"].Rows[0];
                string     employee_id = dr["id"].ToString();
                string     salary      = "insert into salary(employee_id,basic_salary,allowance, insurance) values(" + employee_id + ",0,0,0)";
                SqlCommand command1    = new SqlCommand(salary);
                command1.Connection = conn;
                command1.ExecuteNonQuery();

                MessageBox.Show("New Employee Added");
                connect.diconnectFromDb(conn);
                this.Close();
            }
            else
            {
                MessageBox.Show("Failed adding New Employee");
            }
        }
        public List <AttendanceArtifact> GetAttendanceCount(string month, string year, string numberOfDays)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();

            string noOfDays = numberOfDays;

            string     employeeQuery = "select * from employee ";
            SqlCommand cmd           = new SqlCommand(employeeQuery);

            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;
            DataSet ds = new DataSet();

            da.Fill(ds, "employee");
            List <AttendanceArtifact> attendanceArtifactList = new List <AttendanceArtifact>();

            foreach (DataRow dr in ds.Tables["employee"].Rows)
            {
                string     empid           = (dr["id"].ToString());
                string     attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + noOfDays + " 00:00:00.000' and present = 'True'";
                SqlCommand cmd1            = new SqlCommand(attendanceQuery);
                cmd1.Connection = conn;
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd1;
                DataSet ds1 = new DataSet();
                da1.Fill(ds1, "attendance");

                AttendanceArtifact attendance = new AttendanceArtifact();

                attendance.Id                  = int.Parse(empid);
                attendance.FirstName           = dr["fname"].ToString();
                attendance.MiddleName          = dr["middlename"].ToString();
                attendance.LastName            = dr["lname"].ToString();
                attendance.Designation         = dr["designation"].ToString();
                attendance.ContactDetails      = dr["contactdetails"].ToString();
                attendance.NumberOfPresentDays = ds1.Tables["attendance"].Rows.Count.ToString();
                attendanceArtifactList.Add(attendance);
                connect.diconnectFromDb(conn);
            }
            return(attendanceArtifactList);
        }
        private void delete_button_Click(object sender, EventArgs e)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();
            string     query            = "delete from employee where id = " + empid_txtbox.Text;
            SqlCommand cmd = new SqlCommand(query);

            cmd.Connection = conn;
            int result = cmd.ExecuteNonQuery();

            if (result > 0)
            {
                MessageBox.Show("Successfully Deleted");
            }
            else
            {
                MessageBox.Show("Error");
            }
        }
Esempio n. 10
0
        private void add_button_Click(object sender, EventArgs e)
        {
            bool workstate;
            if (active.Checked == true)
            {
                workstate = true;
            }
            else
            {
                workstate = false;
            }
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
            string query = "insert into employee(fname,lname,middlename,designation,address,contactdetails,active,deleted,dateofbirth,qualification) values ('" + fname_txtbox.Text+"','"+ lname_txtbox.Text + "','" + mname_txtbox.Text +"','"+ designation_txtbox.Text +"','" + address_txtbox.Text+"','"+ contact_txtbox.Text + "','"+workstate+"','False','" + dob_pick.Value.Date +"','" + qualification_txtbox.Text + "')";
            SqlCommand command = new SqlCommand(query);
            command.Connection = conn;
            int result = command.ExecuteNonQuery();
            if (result > 0)
            {
            string selectid = "select max(id) as id from employee";
            command.CommandText = selectid;
            command.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            DataSet ds = new DataSet();
            da.Fill(ds, "employee_id");
                DataRow dr = ds.Tables["employee_id"].Rows[0];
            string employee_id = dr["id"].ToString();
            string salary = "insert into salary(employee_id,basic_salary,allowance, insurance) values(" + employee_id + ",0,0,0)";
            SqlCommand command1 = new SqlCommand(salary);
            command1.Connection = conn;
            command1.ExecuteNonQuery();

                MessageBox.Show("New Employee Added");
                connect.diconnectFromDb(conn);
                this.Close();

            }
            else
            {
                MessageBox.Show("Failed adding New Employee");
            }
        }
        public List<AttendanceArtifact> GetAttendanceCount(string month, string year, string numberOfDays)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();

            string noOfDays = numberOfDays;

            string employeeQuery = "select * from employee ";
            SqlCommand cmd = new SqlCommand(employeeQuery);
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "employee");
            List<AttendanceArtifact> attendanceArtifactList = new List<AttendanceArtifact>();
            foreach (DataRow dr in ds.Tables["employee"].Rows)
            {
                string empid = (dr["id"].ToString());
                string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + noOfDays + " 00:00:00.000' and present = 'True'";
                SqlCommand cmd1 = new SqlCommand(attendanceQuery);
                cmd1.Connection = conn;
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd1;
                DataSet ds1 = new DataSet();
                da1.Fill(ds1, "attendance");

                AttendanceArtifact attendance = new AttendanceArtifact();

                attendance.Id = int.Parse(empid);
                attendance.FirstName = dr["fname"].ToString();
                attendance.MiddleName = dr["middlename"].ToString();
                attendance.LastName = dr["lname"].ToString();
                attendance.Designation = dr["designation"].ToString();
                attendance.ContactDetails = dr["contactdetails"].ToString();
                attendance.NumberOfPresentDays = ds1.Tables["attendance"].Rows.Count.ToString();
                attendanceArtifactList.Add(attendance);
                connect.diconnectFromDb(conn);

            }
            return attendanceArtifactList;
        }
        public bool UpdateSalaryDetails(SalaryDetails obj)
        {
            DbConnectionManager dbConnectionManager = new DbConnectionManager();
            SqlConnection       connection          = dbConnectionManager.connectToDb();
            string query = "update salary set basic_salary =" + obj.BasicSalary + " ,allowance =" + obj.Allowance + " ,insurance =" + obj.Insurance + " where employee_id =" + obj.Id;


            SqlCommand command = new SqlCommand(query);

            command.Connection = connection;
            int result = command.ExecuteNonQuery();

            if (result > 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 13
0
        public List <EmployeeArtifact> GetAllEmployee()
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection       conn    = connect.connectToDb();
            string     employeeQuery    = "select * from employee ";
            SqlCommand cmd1             = new SqlCommand(employeeQuery);

            cmd1.Connection = conn;
            SqlDataAdapter da1 = new SqlDataAdapter();

            da1.SelectCommand = cmd1;
            DataSet ds1 = new DataSet();

            da1.Fill(ds1, "employee");


            List <EmployeeArtifact> empArtifactList = new List <EmployeeArtifact>();

            foreach (DataRow dr in ds1.Tables["employee"].Rows)
            {
                EmployeeArtifact temp = new EmployeeArtifact();
                temp.DateOfBirth    = dr["dateofbirth"].ToString();
                temp.FirstName      = dr["fname"].ToString();
                temp.Address        = dr["address"].ToString();
                temp.Id             = int.Parse(dr["id"].ToString());
                temp.LastName       = dr["lname"].ToString();
                temp.MiddleName     = dr["middlename"].ToString();
                temp.Designation    = dr["designation"].ToString();
                temp.Qualification  = dr["qualification"].ToString();
                temp.ContactDetails = dr["contactdetails"].ToString();
                temp.Active         = bool.Parse(dr["active"].ToString());
                temp.Deleted        = bool.Parse(dr["deleted"].ToString());
                empArtifactList.Add(temp);
            }
            return(empArtifactList);
        }
Esempio n. 14
0
        public bool CheckLoginCredential(string username, string password)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
            string query = "select * from login " + "where username='******' and password='******'";
            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = conn;

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "login");

            if (ds.Tables["Login"].Rows.Count > 0)
            {
                connect.diconnectFromDb(conn);
                return true;
            }
            else
            {
                connect.diconnectFromDb(conn);
                return false;
            }
        }
Esempio n. 15
0
        public bool EditEmployee(EmployeeArtifact obj)
        {
            string query = "update employee set lname ='" + obj.LastName + "', middlename='" + obj.MiddleName + "' ,designation='" + obj.Designation
                           + "',address='" + obj.Address + "',contactdetails = '" + obj.ContactDetails
                           + "',active = '" + obj.Active + "', deleted= '" + obj.Deleted + "', dateofbirth = '" + obj.DateOfBirth + "',qualification = '" + obj.Qualification + "' where id = " + obj.Id;
            DbConnectionManager dbConnectionManager = new DbConnectionManager();
            SqlConnection       connection          = dbConnectionManager.connectToDb();

            SqlCommand command = new SqlCommand(query);

            command.Connection = connection;
            int result = command.ExecuteNonQuery();

            dbConnectionManager.diconnectFromDb(connection);

            if (result > 0)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Esempio n. 16
0
        public SalaryDetails GetSalaryCredentialsByEmpId(string empId)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
            string salaryQuery = "select * from salary where employee_id = " + empId;
            SqlCommand cmd = new SqlCommand(salaryQuery);
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "salary");
            List<SalaryDetails> salaryArtifactList = new List<SalaryDetails>();

            SalaryDetails salary = new SalaryDetails();
            foreach (DataRow dr in ds.Tables["salary"].Rows)
            {

                salary.Id = int.Parse((dr["employee_id"].ToString()));
                salary.BasicSalary = float.Parse(dr["basic_salary"].ToString());
                salary.Allowance = float.Parse(dr["allowance"].ToString());
                salary.Insurance = float.Parse(dr["insurance"].ToString());

                salaryArtifactList.Add(salary);
            }

            connect.diconnectFromDb(conn);
            return salary;
        }
Esempio n. 17
0
        public bool UpdateSalaryDetails(SalaryDetails obj)
        {
            DbConnectionManager dbConnectionManager = new DbConnectionManager();
            SqlConnection connection = dbConnectionManager.connectToDb();
            string query = "update salary set basic_salary =" + obj.BasicSalary + " ,allowance =" + obj.Allowance + " ,insurance =" + obj.Insurance + " where employee_id =" + obj.Id;

            SqlCommand command = new SqlCommand(query);
            command.Connection = connection;
            int result = command.ExecuteNonQuery();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;

            }
        }
Esempio n. 18
0
        public List<EmployeeArtifact> GetAllEmployee()
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
            string employeeQuery = "select * from employee ";
            SqlCommand cmd1 = new SqlCommand(employeeQuery);
            cmd1.Connection = conn;
            SqlDataAdapter da1 = new SqlDataAdapter();
            da1.SelectCommand = cmd1;
            DataSet ds1 = new DataSet();
            da1.Fill(ds1, "employee");

            List<EmployeeArtifact> empArtifactList = new List<EmployeeArtifact>();
            foreach (DataRow dr in ds1.Tables["employee"].Rows)
            {
                EmployeeArtifact temp = new EmployeeArtifact();
                temp.DateOfBirth = dr["dateofbirth"].ToString();
                temp.FirstName = dr["fname"].ToString();
                temp.Address = dr["address"].ToString();
                temp.Id = int.Parse(dr["id"].ToString());
                temp.LastName = dr["lname"].ToString();
                temp.MiddleName = dr["middlename"].ToString();
                temp.Designation = dr["designation"].ToString();
                temp.Qualification = dr["qualification"].ToString();
                temp.ContactDetails = dr["contactdetails"].ToString();
                temp.Active = bool.Parse(dr["active"].ToString());
                temp.Deleted = bool.Parse(dr["deleted"].ToString());
                empArtifactList.Add(temp);
            }
            return empArtifactList;
        }
 public string GetAttendanceCountByEmpID(string empid, string month, string year, string numberOfDays)
 {
     DbConnectionManager connect = new DbConnectionManager();
     SqlConnection conn = connect.connectToDb();
     string attendanceQuery = "select * from attendance where employee_id = " + empid + " and attendance_date between '" + year + "/" + month + "/1 00:00:00.000' and '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' and present = 'True'";
     SqlCommand cmd1 = new SqlCommand(attendanceQuery);
     cmd1.Connection = conn;
     SqlDataAdapter da1 = new SqlDataAdapter();
     da1.SelectCommand = cmd1;
     DataSet ds1 = new DataSet();
     da1.Fill(ds1, "attendance");
     return ds1.Tables["attendance"].Rows.Count.ToString();
 }
Esempio n. 20
0
        public List<SalaryDetails> GetMonthlySalaryDetails(string month, string year, string numberOfDays)
        {
            DbConnectionManager connect = new DbConnectionManager();
            SqlConnection conn = connect.connectToDb();
                       string employeeQuery = "select * from employee ";
            SqlCommand cmd = new SqlCommand(employeeQuery);
            cmd.Connection = conn;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "employee");
            List<SalaryDetails> monthlySalaryDetails = new List<SalaryDetails>();
            foreach (DataRow dr in ds.Tables["employee"].Rows)
            {

                string empid = (dr["id"].ToString());

                SalaryDetails salary = new SalaryDetails();
                AttendanceArtifact selectedMonth = new AttendanceArtifact();

                SalaryDetails tempObj = salary.GetSalaryCredentialsByEmpId(empid);
                salary.FirstName = dr["fname"].ToString();
                salary.MiddleName = dr["middlename"].ToString();
                salary.LastName = dr["lname"].ToString();
                salary.Designation = dr["designation"].ToString();
                salary.Id = int.Parse(empid);
                salary.BasicSalary = tempObj.BasicSalary;
                salary.NoOfPresentDays = int.Parse(selectedMonth.GetAttendanceCountByEmpID(empid, month, year, numberOfDays));
                salary.MonthlySalary = (salary.BasicSalary * salary.NoOfPresentDays);
                salary.Allowance = (salary.MonthlySalary * (tempObj.Allowance / 100));
                salary.Insurance = (salary.MonthlySalary * (tempObj.Insurance/100));
                salary.NetSalary = (salary.MonthlySalary + salary.Allowance - salary.Insurance);
                monthlySalaryDetails.Add(salary);

                string redundantCheckQuery = "select * from monthlySalary where emp_id = " + int.Parse(empid) + " and month = '" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000'";
                SqlCommand cmd1 = new SqlCommand(redundantCheckQuery);
                cmd1.Connection = conn;
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd1;
                DataSet ds1 = new DataSet();
                da1.Fill(ds1, "monthlySalary");
                if (ds1.Tables["monthlySalary"].Rows.Count == 0)
                {
                    string salaryQuery = "insert into monthlySalary(emp_id,month,monthlySalary,allowance,insurance,netSalary) values( " + int.Parse(empid) + ",'" + year + "/" + month + "/" + numberOfDays + " 00:00:00.000' ," +salary.MonthlySalary+","+salary.Allowance+","+salary.Insurance+","+ salary.NetSalary + ")";
                    SqlCommand command = new SqlCommand(salaryQuery);
                    command.Connection = conn;
                    command.ExecuteNonQuery();

                }
            }
            connect.diconnectFromDb(conn);
            return monthlySalaryDetails;
        }