/** 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);
}
}
}
}
public FormAngajat()
{
InitializeComponent();
MyOracleConnection.getAngId();
MyOracleConnection.collectPontaj(MyOracleConnection.utilizatorId, dataGridPontaj);
labelCounterObjects.Text = dataGridPontaj.RowCount.ToString();
}
/** 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 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);
}
}
}
}
/** 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);
}
private void buttonConectareLogin_Click(object sender, EventArgs e)
{
username = textBoxUserName.Text;
password = textBoxPassword.Text;
if (!(string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password)))
{
if (MyOracleConnection.Connect(username, password))
{
TipUtilizator tipUtilizator = MyOracleConnection.TypeOfUtilizator();
switch (tipUtilizator)
{
case TipUtilizator.Admin:
FormAdmin formAdmin = new FormAdmin();
MyOracleConnection.utilizatorId = username;
formAdmin.setUserLabel(username);
this.Hide();
formAdmin.ShowDialog(this);
this.Show();
break;
case TipUtilizator.Angajat:
FormAngajat formAngajat = new FormAngajat();
MyOracleConnection.utilizatorId = username;
formAngajat.setUserLabel(username);
this.Hide();
formAngajat.ShowDialog(this);
this.Show();
break;
case TipUtilizator.None:
MessageBox.Show("Accesul utilizatorului curent este restrictionat!");
break;
}
MyOracleConnection.Close();
}
else
{
MessageBox.Show("Introduceti datele corect!");
}
}
}
/**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);
}
}
}
}
/** Sterge un element.Pontaj din tabel **/
private void buttonDeleteUserObject_Click(object sender, EventArgs e)
{
// daca tabela de pontaj nu este goala
if (dataGridPontaj.RowCount > 0)
{
string proiectNume = "";
int oraStart = 0;
int oraStop = 0;
int totalOre = 0;
string dateTimePontaj = "";
foreach (DataGridViewRow row in dataGridPontaj.SelectedRows)
{
proiectNume = row.Cells[1].Value.ToString();
oraStart = int.Parse(row.Cells[2].Value.ToString());
oraStop = int.Parse(row.Cells[3].Value.ToString());
totalOre = int.Parse(row.Cells[4].Value.ToString());
dateTimePontaj = row.Cells[5].Value.ToString();
}
int buffer = 3079;
OracleCommand command = MyOracleConnection.con.CreateCommand();
try
{
command.CommandText = "userDBA.ANGAJAT_PACKAGE.FUNC_STERGE_PONTAJ";
// seteaza flag
command.Parameters.Add("flag", OracleDbType.Int32, buffer);
command.Parameters["flag"].Direction = ParameterDirection.ReturnValue;
// asigneaza valori parametrilor
command.Parameters.Add(new OracleParameter("dataPontaj", OracleDbType.Date)).Value = DateTime.ParseExact(dateTimePontaj, "dd-MM-yyyy", CultureInfo.InvariantCulture);
command.Parameters.Add(new OracleParameter("oraStart", OracleDbType.Int32)).Value = oraStart;
command.Parameters.Add(new OracleParameter("oraStop", OracleDbType.Int32)).Value = oraStop;
command.Parameters.Add(new OracleParameter("proiect_nume", OracleDbType.Varchar2)).Value = proiectNume;
command.Parameters.Add(new OracleParameter("numeAngajat", OracleDbType.Varchar2)).Value = MyOracleConnection.utilizatorId;
command.Parameters.Add(new OracleParameter("pro_ore_efec", OracleDbType.Int32)).Value = totalOre;
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
catch (Exception exc)
{
MessageBox.Show("[Eroare-Exception] " + exc);
}
string flag_insert = Convert.ToString(command.Parameters["flag"].Value);
if (flag_insert == "1")
{
MyOracleConnection.collectPontaj(MyOracleConnection.utilizatorId, dataGridPontaj);
MessageBox.Show("[Log] Stergere cu succes.");
}
else
{
MessageBox.Show("[Eroare] Nu s-a sters elementul nou.\n");
}
}
else
{
MessageBox.Show("[Eroare] Nu exista elemente.\nTabel gol.");
}
}
/** Editeaza pontajul **/
private void buttonEditeazaPontaj_Click(object sender, EventArgs e)
{
int oraStart = -1;
int oraStop = -1;
int indexOraStart = comboBoxOraStartEdit.SelectedIndex;
int indexOraStop = comboBoxOraStopEdit.SelectedIndex;
int totalOrePontate = 0;
/** validare date inainte de a executa procedura **/
// daca orele nu sunt selectate
if (indexOraStart == -1 || indexOraStop == -1)
{
MessageBox.Show("[Eroare] Va rugam sa selectati intervalul orar corect.");
}
else
{
oraStart = comboBoxOraStartEdit.Items[indexOraStart].ToString().Length > 0 ? Int32.Parse(comboBoxOraStartEdit.Items[indexOraStart].ToString()) : -1;
oraStop = comboBoxOraStopEdit.Items[indexOraStop].ToString().Length > 0 ? Int32.Parse(comboBoxOraStopEdit.Items[indexOraStop].ToString()) : -1;
totalOrePontate += oraStop - oraStart;
// verifica turele de noapte
if ((oraStop >= 1 && oraStop <= 6) || oraStop == 24)
{
MessageBox.Show("[Eroare] Turele de noapte nu sunt admise.\n Nu se poate ponta in intervalul 24:00 - 6:00");
totalOrePontate = 0;
}
else
{
// verifica daca numarul de ore pontate pe zi este depasit
if (totalOrePontate > 8 || totalOrePontate <= 0)
{
MessageBox.Show("[Eroare] Nu se pot ponta mai mult de 8 h/zi sau mai putin de o 1 h/zi.");
totalOrePontate = 0;
}
else
{
int buffer = 3079;
OracleCommand command = MyOracleConnection.con.CreateCommand();
try
{
command.CommandText = "userDBA.ANGAJAT_PACKAGE.FUNC_EDITEAZA_PONTAJ";
command.Parameters.Add("flag", OracleDbType.Int32, buffer);
command.Parameters["flag"].Direction = ParameterDirection.ReturnValue;
// asigneaza valori parametrilor
Console.Write(projectMapping.FirstOrDefault(x => x.Value == oldValueNumeProiect).Key);
command.Parameters.Add(new OracleParameter("dataPontaj", OracleDbType.Date)).Value = dateTimePickerEdit.Value;
command.Parameters.Add(new OracleParameter("numeAngajat", OracleDbType.Varchar2)).Value = MyOracleConnection.utilizatorId;
command.Parameters.Add(new OracleParameter("idProiect", OracleDbType.Int32)).Value = projectMapping.FirstOrDefault(x => x.Value == oldValueNumeProiect).Key;
command.Parameters.Add(new OracleParameter("oldOraStart", OracleDbType.Int32)).Value = oldValueOraStart;
command.Parameters.Add(new OracleParameter("oldOraStop", OracleDbType.Int32)).Value = oldValueOraStop;
command.Parameters.Add(new OracleParameter("newOraStart", OracleDbType.Int32)).Value = oraStart;
command.Parameters.Add(new OracleParameter("newOraStop", OracleDbType.Int32)).Value = oraStop;
command.Parameters.Add(new OracleParameter("old_pro_ore_efec", OracleDbType.Int32)).Value = oldValueTotalOre;
command.Parameters.Add(new OracleParameter("new_pro_ore_efec", OracleDbType.Int32)).Value = oraStop - oraStart;
// 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":
{
MyOracleConnection.collectPontaj(MyOracleConnection.utilizatorId, parentForm.getGridObject());
MessageBox.Show("[Log] Editare cu succes.");
totalOrePontate = 0;
break;
}
case "1":
{
MessageBox.Show("[Log] Orele pe care le adaugati suprapun alte ore deja pontate.\n Verificati din nou cu atentie!");
totalOrePontate = 0;
break;
}
case "2":
{
MessageBox.Show("[Log] Data not found.");
totalOrePontate = 0;
break;
}
}
}
catch (Exception exc)
{
MessageBox.Show("[Log - Exception] " + exc);
}
}
}
}
}
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!");
}
}
}