private void btnAddPath_Click(object sender, EventArgs e)
{
StreamReader sr;
CsvHelper.CsvReader csvread;
sr = new StreamReader(txtFilePath.Text);
csvread = new CsvHelper.CsvReader(sr);
KlijentIzCsv kicsv = new KlijentIzCsv();
csvread.Configuration.HasHeaderRecord = false;
int i = 1;
while (csvread.Read())
{
if (i == 1)
{
kicsv.klijent = csvread[4].Trim();
kicsv.nazivMonitoringa = csvread[5].Trim();
}
Console.WriteLine(csvread[0]);
if (csvread[0].Trim() != "")
{
kicsv.nicsvList.Add(new NadgledanaIzCsv(csvread[0].Trim(), csvread[1].Trim(), csvread[2].Trim(), csvread[3].Trim()));
}
i++;
}
DBCommOsnovnePostavke.connection = new SqlConnection(DBBlokadeData.connectionString);
DBCommOsnovnePostavke.connection.Open();
DBCommOsnovnePostavke.addKlijentIzCsv(kicsv);
DBCommOsnovnePostavke.connection.Close();
txtMaticniBroj.Clear();
txtNaziv.Clear();
txtFilePath.Clear();
ucitajKlijente(1);
}
public static void addKlijentIzCsv(KlijentIzCsv kicsv)
{
string idKlijent = null;
string idMonitoring = null;
string idNadgledanaFirma = null;
query = "SELECT IDKlijent FROM Klijent WHERE Naziv = @naziv";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@naziv", kicsv.klijent);
reader = command.ExecuteReader();
if (reader.Read())
{
idKlijent = reader[0].ToString().Trim();
}
reader.Close();
if (idKlijent == null)
{
query = "INSERT INTO Klijent (Naziv) OUTPUT INSERTED.IDKlijent VALUES(@naziv)";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@naziv", kicsv.klijent);
idKlijent = command.ExecuteScalar().ToString().Trim();
}
// Za insertovanje monitoringa
query = "SELECT IDMonitoring FROM Monitoring WHERE Naziv = @naziv";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@naziv", kicsv.nazivMonitoringa);
reader = command.ExecuteReader();
if (reader.Read())
{
idMonitoring = reader[0].ToString().Trim();
}
reader.Close();
if (idMonitoring == null)
{
query = "INSERT INTO Monitoring (IDKlijent,IDVrstaMonitoringa,IDTipFajla,Naziv) OUTPUT INSERTED.IDMonitoring " +
"VALUES(@idKlijent,@idVrstaMonitoringa,@idTipFajla,@naziv) ";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@idKlijent", idKlijent);
command.Parameters.AddWithValue("@idVrstaMonitoringa", 1);
command.Parameters.AddWithValue("@idTipFajla", 1);
command.Parameters.AddWithValue("@naziv", kicsv.nazivMonitoringa);
idMonitoring = command.ExecuteScalar().ToString().Trim();
}
List <string> idNadgledanaFirmaList = new List <string>();
foreach (NadgledanaIzCsv nicsv in kicsv.nicsvList)
{
idNadgledanaFirma = null;
query = "SELECT IDNadgledanaFirma FROM NadgledanaFirma WHERE MaticniBroj = @maticniBroj";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@maticniBroj", nicsv.maticniBroj);
reader = command.ExecuteReader();
if (reader.Read())
{
idNadgledanaFirma = reader[0].ToString().Trim();
}
reader.Close();
if (idNadgledanaFirma == null)
{
query = "INSERT INTO NadgledanaFirma(MaticniBroj) OUTPUT INSERTED.IdNadgledanaFirma VALUES(@maticniBroj) ";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@maticniBroj", nicsv.maticniBroj);
idNadgledanaFirma = command.ExecuteScalar().ToString().Trim();
}
query = "SELECT * FROM MonitoringFirma WHERE IDNadgledanaFirma = @idNadgledanaFirma AND IDMonitoring=@idMonitoring";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@idNadgledanaFirma", idNadgledanaFirma);
command.Parameters.AddWithValue("@idMonitoring", idMonitoring);
reader = command.ExecuteReader();
if (reader.Read())
{
reader.Close();
}
else
{
reader.Close();
query = "INSERT INTO MonitoringFirma (IDNadgledanaFirma, IDMonitoring) VALUES (@idNadgledanaFirma,@idMonitoring)";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@idNadgledanaFirma", idNadgledanaFirma);
command.Parameters.AddWithValue("@idMonitoring", idMonitoring);
command.ExecuteNonQuery();
}
query = "SELECT IDOsnovneInformacije FROM OsnovneInformacije WHERE IDNadgledanaFirma = @idNadgledanaFirma";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@idNadgledanaFirma", idNadgledanaFirma);
reader = command.ExecuteReader();
if (reader.Read())
{
reader.Close();
query = "UPDATE OsnovneInformacije SET Naziv = @naziv, Adresa = @adresa, Grad = @grad WHERE IDNadgledanaFirma = @idNadgledanaFirma";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@naziv", (nicsv.naziv == null) ? DBNull.Value : (object)nicsv.naziv);
command.Parameters.AddWithValue("@adresa", (nicsv.adresa == null) ? DBNull.Value : (object)nicsv.adresa);
command.Parameters.AddWithValue("@grad", (nicsv.mesto == null) ? DBNull.Value : (object)nicsv.mesto);
command.Parameters.AddWithValue("@idNadgledanaFirma", idNadgledanaFirma);
command.ExecuteNonQuery();
}
else
{
reader.Close();
query = "INSERT INTO OsnovneInformacije (IDNadgledanaFirma, Naziv, Adresa, Grad) VALUES (@idNadgledanaFirma, @naziv, @adresa, @grad)";
command = new SqlCommand(query, DBCommOsnovnePostavke.connection);
command.Parameters.AddWithValue("@idNadgledanaFirma", idNadgledanaFirma);
command.Parameters.AddWithValue("@naziv", (nicsv.naziv == null) ? DBNull.Value : (object)nicsv.naziv);
command.Parameters.AddWithValue("@adresa", (nicsv.adresa == null) ? DBNull.Value : (object)nicsv.adresa);
command.Parameters.AddWithValue("@grad", (nicsv.mesto == null) ? DBNull.Value : (object)nicsv.mesto);
command.ExecuteNonQuery();
}
}
}
