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();
}
}