/** Adauga departamentele in interfata **/ public void setDepartamente() { string query = @"SELECT dept_id, dept_nume FROM userDBA.departamente"; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { int index = 0; string departamentName = ""; int departamentID = 0; while (reader.Read()) { try { departamentID = reader.GetInt32(0); departamentName = reader.GetString(1); // mapare departamente impreuna cu id-ul departamentMapping.Add(departamentID, departamentName); comboBoxDepartament.Items.Insert(index++, departamentName); } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare departamente:" + ex.Message); } } } }
/** Populare interfata cu proiectele pe care user-ul poate ponta **/ public void populareComboBoxProiecte() { string query = @"SELECT t1.PRO_ID, t1.PRO_NUME, lower(t4.ANG_NUME), t5.COMP_DENUMIRE FROM userDBA.PROIECTE t1 INNER JOIN userDBA.PROIECTE_COMPETENTE t2 on t1.pro_id = t2.pro_id INNER JOIN userDBA.ANGAJATI_COMPETENTE t3 on t3.comp_id = t2.comp_id INNER JOIN userDBA.ANGAJATI t4 on t4.ang_id = t3.ang_id INNER JOIN userDBA.COMPETENTE t5 on t5.comp_id = t2.comp_id WHERE lower(t4.ang_nume) = '" + MyOracleConnection.utilizatorId + "'"; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { int index = 0; string projectName = ""; int projectID = 0; while (reader.Read()) { try { projectID = reader.GetInt32(0); projectName = reader.GetString(1); // mapare proiecte impreuna cu id-ul projectMapping.Add(projectID, projectName); comboBoxProiectEdit.Items.Insert(index++, projectName); } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare Proiecte:" + ex.Message); } } } }
/** Afisare Stadiu Proiecte **/ public void afisareStadiuProiecte() { string query = @"SELECT PRO_NUME, PRO_ORE_ESTIMATE, PRO_ORE_EFECTUATE, PRO_STADIU FROM userDBA.PROIECTE"; string[] row = { "", "", "", "" }; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { try { row[0] = reader.GetString(0); row[1] = reader.GetInt32(1).ToString(); row[2] = reader.GetInt32(2).ToString(); row[3] = reader.GetString(3); dataGridViewProiecte.Rows.Add(row); } catch (Exception ex) { MessageBox.Show("[Eroare] Afisare stadiu Proiecte:" + ex.Message); } } } labelCounterObjects.Text = dataGridViewProiecte.RowCount.ToString(); }
/** Adauga competentele in interfata **/ public void setCompetente() { string query = @"SELECT comp_id, comp_denumire FROM userDBA.competente"; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { string competenta = ""; int competentaID = 0; while (reader.Read()) { try { competentaID = reader.GetInt32(0); competenta = reader.GetString(1); // mapare competente impreuna cu id-ul din BD competenteMapping.Add(competentaID, competenta); checkedListBoxCompetente.Items.Add(competenta); } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare competente:" + ex.Message); } } } }
public FormInfoAngajat(DataGridView dataGrid) : this() { foreach (DataGridViewRow row in dataGrid.SelectedRows) { angID = int.Parse(row.Cells[0].Value.ToString()); labelNume.Text = row.Cells[1].Value.ToString(); labelAngajare.Text = row.Cells[3].Value.ToString(); labelParasirii.Text = row.Cells[4].Value.ToString() != "null" ? row.Cells[4].Value.ToString() : "-- Stadiu Activ --"; labelDepartament.Text = row.Cells[5].Value.ToString(); labelStatus.Text = row.Cells[6].Value.ToString(); } if (labelStatus.Text == "inactiv") { labelStatus.ForeColor = System.Drawing.Color.DarkRed; } else { labelStatus.ForeColor = System.Drawing.Color.ForestGreen; } char[] delimiters = { ',' }; string userName = labelNume.Text.Split(delimiters)[0].ToLower(); string query = @"SELECT t1.PRO_NUME, t5.COMP_DENUMIRE FROM userDBA.PROIECTE t1 INNER JOIN userDBA.PROIECTE_COMPETENTE t2 on t1.pro_id = t2.pro_id INNER JOIN userDBA.ANGAJATI_COMPETENTE t3 on t3.comp_id = t2.comp_id INNER JOIN userDBA.ANGAJATI t4 on t4.ang_id = t3.ang_id INNER JOIN userDBA.COMPETENTE t5 on t5.comp_id = t2.comp_id WHERE t4.ang_id = " + angID; string[] rowToAdd = { "" }; string[] rowToAdd_2 = { "" }; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { try { rowToAdd[0] = reader.GetString(0); dataGridViewCompetente.Rows.Add(rowToAdd); rowToAdd_2[0] = reader.GetString(1); dataGridViewProiecteAng.Rows.Add(rowToAdd_2); } catch (Exception ex) { MessageBox.Show("[Eroare] Afisare Competente:" + ex.Message); } } } }
/** Adauga competentele in interfata **/ public void setCompetente() { //checkedListBox1.SetItemCheckState(0, CheckState.Indeterminate); string query = @"SELECT comp_id, comp_denumire FROM userDBA.competente"; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { string competenta = ""; int competentaID = 0; while (reader.Read()) { try { competentaID = reader.GetInt32(0); competenta = reader.GetString(1); // mapare competente impreuna cu id-ul competenteMapping.Add(competentaID, competenta); checkedListBoxCompetente.Items.Add(competenta); } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare competente:" + ex.Message); } } } query = @"SELECT T1.comp_denumire FROM userDBA.competente T1 INNER JOIN userDBA.angajati_competente T2 ON t1.comp_id = T2.comp_id INNER JOIN userDBA.angajati T3 ON T3.ang_id = t2.ang_id WHERE T3.ang_id = " + angID; using (OracleDataReader reader_2 = MyOracleConnection.ExecuteQuery(query)) { string competenta = ""; while (reader_2.Read()) { try { competenta = reader_2.GetString(0); for (int index = 0; index < checkedListBoxCompetente.Items.Count; index++) { if (checkedListBoxCompetente.Items[index].ToString() == competenta) { checkedListBoxCompetente.SetItemCheckState(index, CheckState.Checked); } } } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare competente:" + ex.Message); } } } }
/** Afisare Angajati **/ public void afisareAngajati() { dataGridAngajati.Rows.Clear(); string query = @"SELECT T1.ANG_ID, T1.ANG_NUME || ', ' || T1.ANG_PRENUME, T1.ANG_SALARIU, T1.ANG_DATA_ANGAJARE, T1.ANG_DATA_PARASIRE, T2.DEPT_NUME FROM userDBA.ANGAJATI T1 INNER JOIN userDBA.DEPARTAMENTE T2 ON T1.DEPT_ID = T2.DEPT_ID"; string[] row = { "", "", "", "", "", "", "" }; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { try { row[0] = reader.GetInt32(0).ToString(); //id row[1] = reader.GetString(1); //nume/prenume row[2] = reader.GetInt32(2).ToString(); //salariu row[3] = reader.GetDateTime(3).ToString("dd/MM/yyyy"); //data angajare row[5] = reader.GetString(5); //departament row[6] = "inactiv"; //stadiul if (!reader.IsDBNull(4)) { row[4] = reader.GetDateTime(4).ToString("dd/MM/yyyy"); //data parasirii firmei } else { row[4] = "null"; row[6] = "activ"; } dataGridAngajati.Rows.Add(row); } catch (Exception ex) { MessageBox.Show("[Eroare] Afisare angajati:" + ex.Message); } } } labelCounterObjects.Text = dataGridAngajati.RowCount.ToString(); angajatIdBD = int.Parse(dataGridAngajati.RowCount.ToString()); dataGridAngajati.Sort(dataGridAngajati.Columns[6], ListSortDirection.Ascending); }
/**Colectare date pentru angajat **/ public static void collectPontaj(string username, DataGridView dataGridPontaj) { dataGridPontaj.Rows.Clear(); string query = @"SELECT T2.ANG_NUME || ', ' || T2.ANG_PRENUME, T3.PRO_NUME, T1.PONTAJ_ORA_START, T1.PONTAJ_ORA_END, (T1.PONTAJ_ORA_END-T1.PONTAJ_ORA_START) AS ORE, T1.PONTAJ_ZI FROM userDBA.PONTAJ T1 INNER JOIN userDBA.ANGAJATI T2 ON T1.ANG_ID = T2.ANG_ID INNER JOIN userDBA.PROIECTE T3 on T3.PRO_ID = T1.PRO_ID WHERE lower(T2.ANG_NUME) = '" + username + "'"; string[] row = { "", "", "", "", "", "" }; using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { try { row[0] = reader.GetString(0); row[1] = reader.GetString(1); row[2] = reader.GetInt32(2).ToString(); row[3] = reader.GetInt32(3).ToString(); row[4] = reader.GetInt32(4).ToString(); row[5] = reader.GetDateTime(5).ToString("dd-MM-yyyy"); dataGridPontaj.Rows.Add(row); } catch (Exception ex) { MessageBox.Show("[Eroare] Colectare Pontaj: " + ex.Message); } } } }
private void loadInfoAng() { //-------Set label for NUME, PRENUME, SALAR DEFAULT, DEPARTAMENT string query = @"SELECT T1.ANG_NUME , T1.ANG_PRENUME, T1.ANG_SALARIU, T2.DEPT_NUME FROM userDBA.ANGAJATI T1 INNER JOIN userDBA.DEPARTAMENTE T2 ON T1.DEPT_ID = T2.DEPT_ID WHERE T1.ANG_ID = " + MyOracleConnection.angID; try { using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { labelNume.Text = reader.GetString(0) + " " + reader.GetString(1); labelSalarDefault.Text = reader.GetInt32(2).ToString() + " RON"; labelDepartament.Text = reader.GetString(3); } } } catch (Exception ex) { MessageBox.Show("[Eroare 01] Afisare info salariu:" + ex.Message); } //-------Set label for ORE LUCRATE query = @"SELECT SUM(pontaj_ora_end-pontaj_ora_start) FROM userDBA.PONTAJ WHERE to_char(pontaj_zi,'MM') = to_char(ADD_MONTHS(SYSDATE, -1),'MM') AND ANG_ID = " + MyOracleConnection.angID; try { using (OracleDataReader reader = MyOracleConnection.ExecuteQuery(query)) { while (reader.Read()) { labelOreLucrate.Text = reader.GetInt32(0).ToString(); } } } catch (Exception ex) { //MessageBox.Show("[Eroare 02] Afisare info salariu:" + ex.Message); } //-------Set label for Salariu final int buffer = 4096; OracleCommand command = MyOracleConnection.con.CreateCommand(); try { command.CommandText = "userDBA.ANGAJAT_PACKAGE.FUNC_ANGAJAT_SALAR"; command.Parameters.Add("salar", OracleDbType.Double, buffer); command.Parameters["salar"].Direction = ParameterDirection.ReturnValue; // asigneaza valori parametrilor command.Parameters.Add(new OracleParameter("var_ang_id", OracleDbType.Double)).Value = MyOracleConnection.angID; // executa funtia command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); string finalSalary = Convert.ToString(command.Parameters["salar"].Value); // verifica ce returneaza functia pl/sql if (finalSalary != "null") { labelSalarFinal.Text = finalSalary + " RON"; } else { labelSalarFinal.Text = "0 RON"; } } catch (Exception exc) { MessageBox.Show("[Log - Exception] " + exc); } }
/** Editare competente **/ private void buttonEditareComp_Click(object sender, EventArgs e) { string query = @"DELETE FROM userDBA.ANGAJATI_COMPETENTE WHERE ang_id = " + angID; OracleDataReader reader = MyOracleConnection.ExecuteQuery(query); int idCompetenta = -1; int buffer = 3079; bool flag_ok = false; if (checkedListBoxCompetente.CheckedItems.Count == 0) { MessageBox.Show("[Log] Angajatul trebuie sa aiba cel putin o competenta!"); } else { foreach (object itemChecked in checkedListBoxCompetente.CheckedItems) { idCompetenta = competenteMapping.FirstOrDefault(x => x.Value == itemChecked.ToString()).Key; try { OracleCommand command = MyOracleConnection.con.CreateCommand(); command.CommandText = "userDBA.ADMIN_PACKAGE.FUNC_UPDATE_COMP"; command.Parameters.Add("flag", OracleDbType.Int32, buffer); command.Parameters["flag"].Direction = ParameterDirection.ReturnValue; // asigneaza valori parametrilor command.Parameters.Add(new OracleParameter("id_angajat", OracleDbType.Int32)).Value = angID; command.Parameters.Add(new OracleParameter("id_comp", OracleDbType.Int32)).Value = idCompetenta; // executa funtia command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); string flag_insert = Convert.ToString(command.Parameters["flag"].Value); // verifica ce returneaza functia pl/sql switch (flag_insert) { case "0": { flag_ok = true; break; } } command.Cancel(); } catch (Exception exc) { MessageBox.Show("[Log - Exception] " + exc); } } if (flag_ok == true) { MessageBox.Show("[Log] Update competente cu succes!"); } } }