private List <string[]> filtrirajListu(string kriterijumZaPretragu) { List <string[]> rez = new List <string[]>(); if (lekovi) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { if (cbKolona.SelectedItem.ToString().Equals("Naziv")) { sql = "SELECT * FROM LEK WHERE LOWER(NAZLEK) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Originalna količina leka")) { sql = "SELECT * FROM LEK WHERE LOWER(OR_KOLICINA_LEKA) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Šifra")) { sql = "SELECT * FROM LEK WHERE LOWER(SIFL) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Cena leka")) { sql = "SELECT * FROM LEK WHERE LOWER(CENA_LEKA) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZLEK"].ToString(), reader["OR_KOLICINA_LEKA"].ToString(), reader["SIFL"].ToString(), reader["CENA_LEKA"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (dijagnoze) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { if (cbKolona.SelectedItem.ToString().Equals("Šifra")) { sql = "SELECT * FROM DIJAGNOZA WHERE LOWER(SIFDIJAG) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Naziv")) { sql = "SELECT * FROM DIJAGNOZA WHERE LOWER(NAZDIJAG) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["SIFDIJAG"].ToString(), reader["NAZDIJAG"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (pacijenti) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { if (cbKolona.SelectedItem.ToString().Equals("Ime")) { sql = "SELECT * FROM PACIJENT WHERE LOWER(IMEP) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Prezime")) { sql = "SELECT * FROM PACIJENT WHERE LOWER(PRZP) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("JMBG")) { sql = "SELECT * FROM PACIJENT WHERE LOWER(JMBGP) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["IMEP"].ToString(), reader["PRZP"].ToString(), reader["JMBGP"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (lekari) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { if (cbKolona.SelectedItem.ToString().Equals("Ime")) { sql = "SELECT * FROM LEKAR_OPSTE_MEDICINE WHERE LOWER(IME) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Prezime")) { sql = "SELECT * FROM LEKAR_OPSTE_MEDICINE WHERE LOWER(PRZ) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["IME"].ToString(), reader["PRZ"].ToString(), reader["ID_ZR"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (ustanove) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM USTANOVE WHERE LOWER(NAZUST) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZUST"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { if (cbKolona.SelectedItem.ToString().Equals("Naziv anamneze")) { sql = "SELECT * FROM ANAM WHERE LOWER(NAZAN) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } else if (cbKolona.SelectedItem.ToString().Equals("Opis anamneze")) { sql = "SELECT * FROM ANAM WHERE LOWER(OPISAN) LIKE '%" + kriterijumZaPretragu.ToLower() + "%'"; } command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZAN"].ToString(), reader["OPISAN"].ToString() }; rez.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } return(rez); }
private void popuniGrid() { dgwSpisak.Rows.Clear(); if (lekovi) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM LEK"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZLEK"].ToString(), reader["OR_KOLICINA_LEKA"].ToString(), reader["SIFL"].ToString(), reader["CENA_LEKA"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (dijagnoze) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM DIJAGNOZA"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["SIFDIJAG"].ToString(), reader["NAZDIJAG"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (pacijenti) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM PACIJENT"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["IMEP"].ToString(), reader["PRZP"].ToString(), reader["JMBGP"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else if (lekari) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM LEKAR_OPSTE_MEDICINE"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["IME"].ToString(), reader["PRZ"].ToString(), reader["ID_ZR"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } dgwSpisak.ClearSelection(); connection.Close(); } } else if (ustanove) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM USTANOVE"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZUST"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } else { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; try { sql = "SELECT * FROM ANAM"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); } catch (Exception) { throw; } try { while (reader.Read()) { String[] podaci = { reader["NAZAN"].ToString(), reader["OPISAN"].ToString() }; dgwSpisak.Rows.Add(podaci); } } catch (Exception) { throw; } connection.Close(); } } dgwSpisak.ClearSelection(); }
private void EvidencijaPacijenta_Load(object sender, EventArgs e) { if (prijem_pacijenta) { string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; string sql = "SELECT * FROM PACIJENT WHERE JMBGP='" + jmbg_pacijenta + "'"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); while (reader.Read()) { tbIme.Text = reader["IMEP"].ToString(); tbIme.ReadOnly = true; tbPrz.Text = reader["PRZP"].ToString(); tbPrz.ReadOnly = true; mtbJmbg.Text = jmbg_pacijenta; mtbJmbg.ReadOnly = true; tbAdr.Text = reader["ADRP"].ToString().Split(',').First(); tbAdr.ReadOnly = true; tbGrad.Text = reader["ADRP"].ToString().Split(',').Last(); tbGrad.ReadOnly = true; tbDrz.Text = reader["DRZP"].ToString(); tbDrz.ReadOnly = true; tbZanimanje.Text = reader["ZANIMANJEP"].ToString(); tbZanimanje.ReadOnly = true; tbPosao.Text = reader["POSAOP"].ToString(); tbPosao.ReadOnly = true; tbTel.Text = reader["BRTELP"].ToString(); tbTel.ReadOnly = true; mtbZK.Text = reader["BRZK"].ToString(); mtbZK.ReadOnly = true; tbNosOsig.Text = reader["NOSOSIG"].ToString(); tbNosOsig.ReadOnly = true; tbFakt.Text = reader["FAKTURA"].ToString(); tbFakt.ReadOnly = true; mtbLbo.Text = reader["LBO"].ToString(); mtbLbo.ReadOnly = true; mtbDatRodj.Text = reader["DATRP"].ToString().TrimEnd('0', ':'); mtbDatRodj.Enabled = false; if (reader["POLP"].ToString().Equals("m")) { rbM.Checked = true; } else { rbZ.Checked = true; } rbM.Enabled = false; rbZ.Enabled = false; if (reader["PARTICIP"].ToString().Equals("1")) { chbParticip.Checked = true; } else { chbParticip.Checked = false; } chbParticip.Enabled = false; btnSac.Visible = false; btnPon.Text = "Zatvori"; } sql = "SELECT * FROM IZBOR_LEKARA,LEKAR_OPSTE_MEDICINE WHERE JMBGP='" + jmbg_pacijenta + "'" + " AND IZBOR_LEKARA.ID_ZR=LEKAR_OPSTE_MEDICINE.ID_ZR AND IZBOR_LEKARA.JMBGZR=LEKAR_OPSTE_MEDICINE.JMBGZR"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); if (reader.Read()) { btnIzbLek.Visible = false; lbIzLekar.Text = reader["IME"].ToString() + " " + reader["PRZ"].ToString() + " " + reader["ID_ZR"].ToString(); } connection.Close(); } } }
private void btnSac_Click(object sender, EventArgs e) { OracleCommand command; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); string sql = ""; string pol = ""; if (rbM.Checked) { pol = "m"; } else { pol = "z"; } int particip; if (chbParticip.Checked) { particip = 1; } else { particip = 0; } sql = "INSERT INTO PACIJENT(IMEP, PRZP, JMBGP, ADRP, DATRP, POLP, DRZP, ZANIMANJEP, POSAOP, BRTELP,BRZK, NOSOSIG, LBO, PARTICIP, FAKTURA) VALUES ('" + tbIme.Text + "','" + tbPrz.Text + "','" + mtbJmbg.Text + "','" + tbAdr.Text + " " + tbGrad.Text + "','" + mtbDatRodj.Value.ToString("dd-MMM-yyyy") + "','" + pol + "','" + tbDrz.Text + "','" + tbZanimanje.Text + "','" + tbPosao.Text + "','" + tbTel.Text + "','" + mtbZK.Text + "','" + tbNosOsig.Text + "','" + mtbLbo.Text + "','" + particip + "','" + tbFakt.Text + "')"; try { command = new OracleCommand(sql); command.Connection = connection; command.ExecuteNonQuery(); } catch (Exception) { throw; } ReceptSnim rs = new ReceptSnim("Uspešno snimljeni podaci o pacijentu"); // rs.ShowDialog(); this.Opacity = .70; DialogResult dr = rs.ShowDialog(); if (dr == DialogResult.Cancel) { //this.Opacity = 1; this.Close(); } // label6.Text = sql; connection.Close(); if (saForme.Equals("sa_protokola")) { Protokol.jmbg_pac = mtbJmbg.Text; } this.DialogResult = DialogResult.OK; } }
private void popuniGrid() { if (!sifLeka.Equals("")) { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; OracleDataReader reader2; try { sql = "SELECT * FROM LEK WHERE SIFL='" + sifLeka + "'"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); if (reader.Read()) { red[i] = reader["NAZLEK"].ToString() + " " + reader["SIFL"].ToString() + ", CENA: " + reader["CENA_LEKA"].ToString(); i++; sql = "SELECT * FROM LEK WHERE GRUPA IS NOT NULL AND SIFL!='" + sifLeka + "' AND CENA_LEKA<'" + reader["CENA_LEKA"].ToString() + "' AND GRUPA='" + reader["GRUPA"].ToString() + "' ORDER BY CENA_LEKA ASC"; command = new OracleCommand(sql); command.Connection = connection; reader2 = command.ExecuteReader(); red[i] = ""; while (reader2.Read()) { red[i] += reader2["NAZLEK"].ToString() + " " + reader2["SIFL"].ToString() + ", CENA: " + reader2["CENA_LEKA"].ToString() + Environment.NewLine; } i = 0; dgwSpisak.Rows.Add(red); } } catch (Exception) { throw; } dgwSpisak.ClearSelection(); connection.Close(); } } else { string sql = ""; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; OracleDataReader reader1; OracleDataReader reader2; try { sql = "SELECT SIFL FROM RECEPT WHERE IZNOS IS NOT NULL AND extract(month from DATIZD) = '" + DateTime.ParseExact(mesec, "MMM", System.Globalization.CultureInfo.InvariantCulture).Month + "'" + "AND ID_ZR='" + id_lekara + "' ORDER BY IZNOS DESC"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); while (reader.Read()) { sql = "SELECT * FROM LEK WHERE SIFL='" + reader["SIFL"].ToString() + "'"; command = new OracleCommand(sql); command.Connection = connection; reader1 = command.ExecuteReader(); if (reader1.Read()) { red[i] = reader1["NAZLEK"].ToString() + " " + reader1["SIFL"].ToString() + ", CENA: " + reader1["CENA_LEKA"].ToString(); i++; sql = "SELECT * FROM LEK WHERE GRUPA IS NOT NULL AND SIFL!='" + reader1["SIFL"].ToString() + "' AND CENA_LEKA<'" + reader1["CENA_LEKA"].ToString() + "' AND GRUPA='" + reader1["GRUPA"].ToString() + "' ORDER BY CENA_LEKA ASC"; command = new OracleCommand(sql); command.Connection = connection; reader2 = command.ExecuteReader(); red[i] = ""; while (reader2.Read()) { red[i] += reader2["NAZLEK"].ToString() + " " + reader2["SIFL"].ToString() + ", CENA: " + reader2["CENA_LEKA"].ToString() + Environment.NewLine; } i = 0; } dgwSpisak.Rows.Add(red); } } catch (Exception) { throw; } dgwSpisak.ClearSelection(); connection.Close(); } } }
private void btnPrikaz_Click(object sender, EventArgs e) { dgwPrimPac.ClearSelection(); string sql = ""; String[] lekibrpac = new String[4]; int i = 0; string connectionString = DBConnection.GetConnectionString(); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command; OracleDataReader reader; OracleDataReader reader1; OracleDataReader reader2; try { sql = "SELECT count(*) FROM RECEPT WHERE extract(month from DATIZD) = '" + DateTime.ParseExact(cbMes.SelectedItem.ToString(), "MMM", System.Globalization.CultureInfo.InvariantCulture).Month + "'"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); if (reader.Read()) { lbUkRec.Text += " " + reader[0].ToString(); } sql = "SELECT SUM(IZNOS) FROM RECEPT WHERE extract(month from DATIZD) = '" + DateTime.ParseExact(cbMes.SelectedItem.ToString(), "MMM", System.Globalization.CultureInfo.InvariantCulture).Month + "'"; command = new OracleCommand(sql); command.Connection = connection; reader = command.ExecuteReader(); if (reader.Read()) { lbUkIznos.Text += " " + reader[0].ToString(); } } catch (Exception) { throw; } #region po_lekarima try { sql = "SELECT DISTINCT ID_ZR FROM LEKAR_OPSTE_MEDICINE"; command = new OracleCommand(sql); command.Connection = connection; reader1 = command.ExecuteReader(); while (reader1.Read()) { sql = "SELECT * FROM LEKAR_OPSTE_MEDICINE WHERE ID_ZR='" + reader1["ID_ZR"].ToString() + "'"; command = new OracleCommand(sql); command.Connection = connection; reader2 = command.ExecuteReader(); if (reader2.Read()) { lekibrpac[i] = reader2["IME"].ToString() + " " + reader2["PRZ"].ToString() + " " + reader2["ID_ZR"].ToString(); i++; } sql = "SELECT count(*) FROM RECEPT WHERE extract(month from DATIZD) = '" + DateTime.ParseExact(cbMes.SelectedItem.ToString(), "MMM", System.Globalization.CultureInfo.InvariantCulture).Month + "'" + "AND ID_ZR='" + reader1["ID_ZR"].ToString() + "'"; command = new OracleCommand(sql); command.Connection = connection; reader2 = command.ExecuteReader(); if (reader2.Read()) { lekibrpac[i] = reader2[0].ToString(); i++; } sql = "SELECT SUM(IZNOS) FROM RECEPT WHERE extract(month from DATIZD) = '" + DateTime.ParseExact(cbMes.SelectedItem.ToString(), "MMM", System.Globalization.CultureInfo.InvariantCulture).Month + "'" + "AND ID_ZR='" + reader1["ID_ZR"].ToString() + "'"; command = new OracleCommand(sql); command.Connection = connection; reader2 = command.ExecuteReader(); if (reader2.Read()) { lekibrpac[i] = reader2[0].ToString(); i++; string izn = reader2[0].ToString(); double prekoraceno = 0; if (!izn.Equals("")) { double iznos = Double.Parse(izn); if (DBConnection.dozvoljeno - iznos <= 0) { prekoraceno = iznos - DBConnection.dozvoljeno; } } lekibrpac[i] = prekoraceno.ToString(); i = 0; } dgwPrimPac.Rows.Add(lekibrpac); foreach (DataGridViewRow dgvr in dgwPrimPac.Rows) { if (dgvr.Cells[1].Value.ToString() == ("0")) { dgwPrimPac.Rows.Remove(dgvr); } else if (dgvr.Cells[3].Value.ToString() != ("0")) { dgvr.DefaultCellStyle.BackColor = Color.LightCoral; } } dgwPrimPac.ClearSelection(); } } catch (Exception) { throw; } #endregion this.Refresh(); connection.Close(); } }