Exemple #1
0
        public void defaultOtTable()
        {
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    q4           = new MySqlCommand("select e.EmpId,FirstName,LastName,o.OTHours from employee e LEFT OUTER JOIN ot o ON e.EmpId = o.EmpId where o.Month = @mon;", connDatabase);

            q4.Parameters.AddWithValue("@mon", "January");

            try
            {
                //MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = q4;
                //DataTable dataset = new DataTable();
                datasetOT.Clear();
                sda.Fill(datasetOT);
                //BindingSource bSource = new BindingSource();

                bindingSource1.DataSource = datasetOT;
                dataGridView4.DataSource  = bindingSource1;
                //sda.Update(dataset);
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #2
0
        public void refreshEmpLeaveTable()
        {
            if (tabSendLeaveRequests.Text == "Send Leave Requests")
            {
                MySqlConnection connDatabase = ConnectionOld.getConnection();
                MySqlCommand    q4           = new MySqlCommand("select RequestedDate,StartDate,EndDate,NoOfDays,Type,Reason,Status from leave_view where EmpId = @empid ;", connDatabase);
                q4.Parameters.AddWithValue("@empId", Connection.getUserIdFromConnectionString());
                try
                {
                    MySqlDataAdapter sda = new MySqlDataAdapter();
                    sda.SelectCommand = q4;
                    DataTable dataset = new DataTable();
                    sda.Fill(dataset);
                    BindingSource bSource = new BindingSource();

                    bSource.DataSource       = dataset;
                    dataGridView1.DataSource = bSource;
                    //sda.Update(dataset);
                }

                catch (MySqlException ex)
                {
                    PanException.Show(this.MdiParent, ex);
                }
            }
        }
Exemple #3
0
        public void getNoOfLeaveDays()
        {
            try
            {
                String          a      = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Annual' ;";
                String          c      = "select COALESCE(sum(NoOfDays),0) from itp.leave_view where EmpId = '" + Connection.getUserIdFromConnectionString() + "' and Status = 'Accepted' and Type = 'Casual';";
                MySqlConnection MyConn = ConnectionOld.getConnection();
                MySqlCommand    SelectCommandAnnual = new MySqlCommand(a, MyConn);
                MySqlCommand    SelectCommandCasual = new MySqlCommand(c, MyConn);
                MySqlDataReader MyReader;
                MyConn.Open();
                MyReader = SelectCommandAnnual.ExecuteReader();

                if (MyReader.Read())
                {
                    labRemAnnual.Text = (14 - MyReader.GetInt32(0)).ToString();
                }
                MyConn.Close();
                MyReader = null;
                MyConn.Open();
                MyReader = SelectCommandCasual.ExecuteReader();

                if (MyReader.Read())
                {
                    int x;

                    x = MyReader.GetInt32(0);
                    labRemCasual.Text = (7 - x).ToString();
                }
            }
            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #4
0
        public void refreshHRLeaveTable()
        {
            if (tabCheckLeaveRequests.Text == "Check Leave Requests")
            {
                MySqlConnection connDatabase = ConnectionOld.getConnection();
                MySqlCommand    q4           = new MySqlCommand("select EmpId,RequestedDate,StartDate,EndDate,NoOfDays,Type,Reason,Status from leave_view where Status = @status;", connDatabase);
                q4.Parameters.AddWithValue("@status", "Pending");

                try
                {
                    MySqlDataAdapter sda = new MySqlDataAdapter();
                    sda.SelectCommand = q4;
                    DataTable dataset = new DataTable();
                    sda.Fill(dataset);
                    BindingSource bSource = new BindingSource();

                    bSource.DataSource       = dataset;
                    dataGridView3.DataSource = bSource;
                    //sda.Update(dataset);
                }

                catch (MySqlException ex)
                {
                    PanException.Show(this.MdiParent, ex);
                }
            }
        }
Exemple #5
0
        private void Rem_Click_1(object sender, EventArgs e)
        {
            if (dataGridView2.SelectedRows.Count > 0)
            {
                DataGridViewRow row = this.dataGridView2.SelectedRows[0];

                emp = row.Cells["EmpId"].Value.ToString();
            }


            String          q2           = "delete from itp.employee where EmpId ='" + emp + "' ";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    cmdDatabase  = new MySqlCommand(q2, connDatabase);
            MySqlDataReader myReader;

            try
            {
                connDatabase.Open();
                myReader = cmdDatabase.ExecuteReader();
                //MessageBox.Show("Record is deleted successfully");
                PanMessage.Show(this.MdiParent, "Record Deleted", "Record is deleted successfully");
                displayDataGridViewSearch();
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #6
0
        public void refreshOtTable()
        {
            //MessageBox.Show("Hello");
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    q4           = new MySqlCommand(@"select e.EmpId,FirstName,LastName,o.OTHours 
                                                    from employee e 
                                                    LEFT OUTER JOIN (
                                                    select EmpId,OTHours from ot where Month = '" + mon + "' ) o ON o.EmpId =e.EmpId;",

                                                            connDatabase);

            //q4.Parameters.AddWithValue("@mon", mon);


            try
            {
                // MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = q4;
                //DataTable dataset = new DataTable();
                datasetOT.Clear();
                sda.Fill(datasetOT);
                //BindingSource bSource = new BindingSource();

                bindingSource1.DataSource = datasetOT;
                dataGridView4.DataSource  = bindingSource1;

                //sda.Update(dataset);
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #7
0
        private void button4_Click(object sender, EventArgs e)
        {
            int Annual = Int32.Parse(labRemAnnual.Text);
            int Casual = Int32.Parse(labRemCasual.Text);


            DateTime endDate   = dateTimePicker2.Value;
            DateTime startDate = dateTimePicker3.Value;

            TimeSpan tspan = endDate - startDate;

            int differenceInDays = tspan.Days;

            if ((this.Type_combo.Text == "Annual") && (differenceInDays > Annual))
            {
                PanException.Show(this.MdiParent, "Annual leave", "You are exceeding the number of remaining annual days");
                return;
            }

            if ((this.Type_combo.Text == "Casual") && (differenceInDays > Casual))
            {
                PanException.Show(this.MdiParent, "Casual leave", "You are exceeding the number of remaining casual days");
                return;
            }



            DateTime start = Convert.ToDateTime(dateTimePicker3.Text);
            DateTime end   = Convert.ToDateTime(dateTimePicker2.Text);

            String          query        = "insert into `leave`(Type,Reason,StartDate,EndDate,RequestedDate,Status,EmpId) values('" + this.Type_combo.Text + "','" + this.Reason.Text + "', @date1 , @date2 , CURDATE(),'Pending', '" + Connection.getUserIdFromConnectionString() + "')";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    cmdDatabase  = new MySqlCommand(query, connDatabase);
            MySqlParameter  param        = new MySqlParameter("@date1", MySqlDbType.Date);

            param.Value = dateTimePicker3.Value;
            MySqlParameter param2 = new MySqlParameter("@date2", MySqlDbType.Date);

            param2.Value = dateTimePicker2.Value;
            cmdDatabase.Parameters.Add(param);
            cmdDatabase.Parameters.Add(param2);

            try
            {
                connDatabase.Open();
                cmdDatabase.ExecuteNonQuery();
                //MessageBox.Show("Records are saved successfully\n");
                PanMessage.Show(this.MdiParent, "Success", "Your leave request has been sent");
                refreshEmpLeaveTable();
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #8
0
        public void Insert()
        {
            isEmpty();

            if (!checkMail(this.Email_txt.Text))
            {
                //MessageBox.Show("Not a valid Email address ");
                PanException.Show(this.MdiParent, "Validation Error", "Not a valid Email address");
                return;
            }
            else if (labNationality.Text == "NIC" && (!checkNIC(this.NIC_txt.Text)))
            {
                //MessageBox.Show("Not a valid NIC Number ");
                PanException.Show(this.MdiParent, "Validation Error", "Not a valid NIC Number");
                return;
            }
            else if (Double.Parse(textBox5.Text) < 10000)
            {
                //MessageBox.Show("Basic Salary should be above 10000");
                PanException.Show(this.MdiParent, "Validation Error", "Basic Salary should be above 10000");
                return;
            }

            /*if (!checkMobile())
             * {
             *  MessageBox.Show("Mobile Number should have 10 digits");
             *  return;
             * }*/
            String          us           = assignUserName();
            int             val          = getNewId();
            Double          sal          = Double.Parse(this.textBox5.Text);
            String          query        = "insert into itp.employee(EmpId,FirstName,LastName,Nationality,Email,MobileNo,NIC,Position,DepId,Gender,DOB,Address,UserName,Password,BasicSalary) values('" + this.Emp_txt.Text + "','" + this.First_txt.Text + "','" + this.Last_txt.Text + "','" + nat + "','" + this.Email_txt.Text + "','" + this.Mob_txt.Text + "','" + this.NIC_txt.Text + "','" + this.Posi_combo.Text + "','" + dep + "','" + gender + "','" + this.dateTimePicker1.Text + "','" + this.Add_txt.Text + "','" + us + "','" + pw + "','" + sal + "'); ";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    cmdDatabase  = new MySqlCommand(query, connDatabase);
            MySqlCommand    createUser   = new MySqlCommand("CREATE_USER", connDatabase);

            createUser.CommandType = CommandType.StoredProcedure;
            createUser.Parameters.AddWithValue("user_id_", this.Emp_txt.Text);
            createUser.Parameters.AddWithValue("password_", pw);
            createUser.Parameters.AddWithValue("name_", this.First_txt.Text + " " + this.Last_txt.Text);

            try
            {
                connDatabase.Open();
                cmdDatabase.ExecuteNonQuery();
                createUser.ExecuteNonQuery();
                //MessageBox.Show("Records are saved successfully\n" + this.First_txt.Text +  this.Last_txt.Text +  " 's Username is " + us);
                PanMessage.Show(this.MdiParent, "Success", "Records are saved successfully\n" + this.First_txt.Text + this.Last_txt.Text + " 's Username is " + us);
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #9
0
        //public void getMonthComboValues()
        //{
        //    String[] m = {"January","February","March","April","May","June","July","August","September","October","November","December"};

        //        MySqlConnection MyConn = ConnectionOld.getConnection();
        //        MySqlCommand SelectCommand = new MySqlCommand("select distinct Month from ot;", MyConn);
        //        MySqlDataReader MyReader;
        //        MyConn.Open();
        //        MyReader = SelectCommand.ExecuteReader();

        //        while (MyReader.Read())
        //        {
        //            String mon = MyReader.GetString("Month");
        //            for (int i = 0; i < m.Length; i++)
        //            {
        //                if (mon != m[0])
        //                {
        //                    combo_Month.Items.Add(mon);
        //                }


        //            }

        //        }
        //}
        public void getDetails()
        {
            MySqlConnection MyConn        = ConnectionOld.getConnection();
            MySqlCommand    SelectCommand = new MySqlCommand("select * from itp.employee where EmpId = '" + Connection.getUserIdFromConnectionString() + "' ;", MyConn);
            MySqlDataReader MyReader;

            MyConn.Open();
            MyReader = SelectCommand.ExecuteReader();

            while (MyReader.Read())
            {
                Emp_txt.Text         = MyReader.GetInt32("EmpId").ToString();
                First_txt.Text       = MyReader.GetString("FirstName");
                Last_txt.Text        = MyReader.GetString("LastName");
                dateTimePicker1.Text = MyReader.GetString("DOB");

                if ((MyReader.GetString("Nationality") == "SriLankan"))
                {
                    combo_Nat.Text = "SriLankan";
                }
                else
                {
                    combo_Nat.Text = "Foreigner";
                    Nat_txt.Text   = MyReader.GetString("Nationality");
                }

                NIC_txt.Text    = MyReader.GetString("NIC");
                Add_txt.Text    = MyReader.GetString("Address");
                Email_txt.Text  = MyReader.GetString("Email");
                Mob_txt.Text    = MyReader.GetString("MobileNo");
                Posi_combo.Text = MyReader.GetString("Position");
                Dep_txt.Text    = MyReader.GetString("DepId");
                textBox5.Text   = MyReader.GetDouble("BasicSalary").ToString();
                String gender = MyReader.GetChar("Gender").ToString();

                if (gender == "M")
                {
                    radioButton1.Checked = true;
                }
                else
                {
                    radioButton2.Checked = true;
                }
            }
        }
Exemple #10
0
        /* public bool checkMobile()
         * {
         *   if (Mob_txt.TextLength < 7 && Mob_txt.TextLength > 15)
         *   {
         *       return false;
         *   }
         *   return true;
         * }
         */
        public int getNewId()
        {
            String          s             = "select max(EmpId) from itp.employee;";
            MySqlConnection MyConn        = ConnectionOld.getConnection();
            MySqlCommand    SelectCommand = new MySqlCommand(s, MyConn);
            MySqlDataReader MyReader;

            MyConn.Open();
            MyReader = SelectCommand.ExecuteReader();

            while (MyReader.Read())
            {
                ss = MyReader.GetInt32("max(EmpId)");
            }

            int n = ss + 1;

            return(n);
        }
Exemple #11
0
        public void setDetails()
        {
            String          q2           = "update itp.employee set FirstName ='" + this.First_txt.Text + "', LastName ='" + this.Last_txt.Text + "',Gender ='" + gender + "', Address ='" + this.Add_txt.Text + "', MobileNo ='" + this.Mob_txt.Text + "', Email ='" + this.Email_txt.Text + "' where EmpId = '" + Connection.getUserIdFromConnectionString() + "' ";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    cmdDatabase  = new MySqlCommand(q2, connDatabase);
            MySqlDataReader myReader;

            try
            {
                connDatabase.Open();
                myReader = cmdDatabase.ExecuteReader();
                //MessageBox.Show("Records are edited successfully");
                PanMessage.Show(this.MdiParent, "Success", "Records are edited successfully");
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #12
0
        private void dataGridView3_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (dataGridView3.SelectedRows.Count > 0)
            {
                DataGridViewRow row = this.dataGridView3.SelectedRows[0];

                emp = row.Cells["EmpId"].Value.ToString();

                MySqlConnection MyConn        = ConnectionOld.getConnection();
                MySqlCommand    SelectCommand = new MySqlCommand("select FirstName,DepId from itp.employee where EmpId = '" + emp + "' ;", MyConn);
                MySqlDataReader MyReader;
                MyConn.Open();
                MyReader = SelectCommand.ExecuteReader();

                while (MyReader.Read())
                {
                    textBox11.Text = MyReader.GetString("FirstName");
                    textBox12.Text = MyReader.GetString("DepId");
                }
            }
        }
Exemple #13
0
        private void button5_Click(object sender, EventArgs e)
        {
            String          q2           = "update itp.leave set Status ='Denied' where EmpId = '" + emp + "' ";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    cmdDatabase  = new MySqlCommand(q2, connDatabase);
            MySqlDataReader myReader;

            try
            {
                connDatabase.Open();
                myReader = cmdDatabase.ExecuteReader();
                //MessageBox.Show("You have rejected the request");
                PanMessage.Show(this.MdiParent, "Leave Request", "Leave request rejected");
                refreshHRLeaveTable();
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #14
0
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection MyConn        = ConnectionOld.getConnection();
                MySqlCommand    SelectCommand = new MySqlCommand("select * from teemah.employee where UserName = '******' and Password = '******' ;", MyConn);
                MySqlDataReader MyReader;
                MyConn.Open();
                MyReader = SelectCommand.ExecuteReader();
                int count = 0;
                while (MyReader.Read())
                {
                    count++;
                }
                if (count == 1)
                {
                    un = MyReader.GetString("UserName");
                    ei = MyReader.GetString("EmpId");
                    //MessageBox.Show("Username and Password is correct");
                    this.Hide();
                    Employee em = new Employee();
                    em.Show();
                }
                else if (count > 1)
                {
                    MessageBox.Show("Duplicate Username and Password are there ");
                }
                else
                {
                    MessageBox.Show("Username and Password is incorrect");
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Exemple #15
0
        public void displayDataGridViewSearch()
        {
            q5 = "select EmpId,FirstName,LastName,Position,DepId,Address,MobileNo,Email from itp.employee ;";
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    q4           = new MySqlCommand(q5, connDatabase);

            try
            {
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = q4;
                dataset           = new DataTable();
                sda.Fill(dataset);
                BindingSource bSource = new BindingSource();

                bSource.DataSource       = dataset;
                dataGridView2.DataSource = bSource;
                //sda.Update(dataset);
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #16
0
        public void refreshSalaryTable()
        {
            MySqlConnection connDatabase = ConnectionOld.getConnection();
            MySqlCommand    q4           = new MySqlCommand("select Month,NetSalary from salary where EmpId = @empid ;", connDatabase);

            q4.Parameters.AddWithValue("@empId", Connection.getUserIdFromConnectionString());
            try
            {
                MySqlDataAdapter sal = new MySqlDataAdapter();
                sal.SelectCommand = q4;
                DataTable dt = new DataTable();
                sal.Fill(dt);
                BindingSource bSal = new BindingSource();

                bSal.DataSource       = dt;
                metroGrid1.DataSource = bSal;
                //sda.Update(dataset);
            }

            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
        }
Exemple #17
0
        private void Employee_Load(object sender, EventArgs e)
        {
            Emp_txt.ReadOnly        = true;
            dateTimePicker3.MinDate = DateTime.Now.AddDays(1);
            dateTimePicker2.MinDate = dateTimePicker3.MinDate.AddDays(1);
            dateTimePicker2.MaxDate = DateTime.Now.AddMonths(2);
            refreshSalaryTable();
            combo_Month.SelectedIndex = 0;
            radioButton1.Enabled      = true;
            radioButton2.Enabled      = true;
            AddOT.TabPages.Remove(tabCheckLeaveRequests);
            AddOT.TabPages.Remove(tabAddOT);
            //AddOT.TabPages.Remove(tabCalSalary);

            MySqlConnection con = ConnectionOld.getConnection();
            MySqlCommand    sc  = new MySqlCommand("select role from user_role_tab where user = @user", con);

            sc.Parameters.AddWithValue("@user", Connection.getUserIdFromConnectionString());
            try
            {
                con.Open();
                MySqlDataReader read = sc.ExecuteReader();
                while (read.Read())
                {
                    string role = read.GetString("role");
                    switch (role)
                    {
                    case "HR Manager": AddNewEmp.Show();
                        Save.Show();
                        AddOT.TabPages.Remove(tabSendLeaveRequests);
                        //AddOT.TabPages.Remove(tabViewSalary);
                        //this.tabViewSalary.Text = "Calculate Salary";
                        AddOT.TabPages.Add(tabCheckLeaveRequests);
                        AddOT.TabPages.Add(tabAddOT);
                        //AddOT.TabPages.Add(tabCalSalary);
                        Rem.Show();
                        //Emp_txt.ReadOnly = false;
                        First_txt.ReadOnly = false;
                        Last_txt.ReadOnly  = false;
                        //radioButton1.Enabled = true;
                        //radioButton2.Enabled = true;
                        dateTimePicker1.Enabled = true;
                        Nat_txt.ReadOnly        = false;
                        NIC_txt.ReadOnly        = false;
                        Add_txt.ReadOnly        = false;
                        Mob_txt.ReadOnly        = false;
                        Email_txt.ReadOnly      = false;
                        Posi_combo.Enabled      = true;
                        textBox5.ReadOnly       = false;
                        combo_Nat.Enabled       = true;
                        break;
                    }
                }
            }
            catch (MySqlException ex)
            {
                PanException.Show(this.MdiParent, ex);
            }
            finally
            {
                con.Close();
            }

            /* first2 = Login.un.Substring(0, 2);
             * if (tabControl1.SelectedIndex == 0 && first2 != "HR")
             * {
             *  AddNewEmp.Hide();
             *  Rem.Hide();
             *  Emp_txt.ReadOnly = true;
             *  First_txt.ReadOnly = true;
             *  Last_txt.ReadOnly = true;
             *  radioButton1.Enabled = false;
             *  radioButton2.Enabled = false;
             *  dateTimePicker1.Enabled = false;
             *  Nat_txt.ReadOnly = true;
             *  NIC_txt.ReadOnly = true;
             *  Add_txt.ReadOnly = false;
             *  Mob_txt.ReadOnly = false;
             *  Email_txt.ReadOnly = false;
             *  Posi_combo.Enabled = false;
             * }
             *
             * if (tabControl1.SelectedIndex == 1 && first2 != "HR")
             * {
             *  Calculate.Hide();
             *  button2.Hide();
             * }
             *
             * if (first2 != "HR")
             * {
             *  tabControl1.TabPages.Remove(tabPage5);
             *  tabControl1.TabPages.Remove(tabPage2);
             * }
             * else
             * {
             *  tabControl1.TabPages.Remove(tabPage3);
             *  this.tabPage2.Text = "Calculate Salary";
             * }
             *
             * if (tabPage4.Text == "Search Employees" && first2 == "HR")
             * {
             *  Rem.Show();
             * }*/
        }