public async static Task <DataSet> ExecuteProcedureReport(string sql, List <NpgsqlParameter> parameters)
{
DataSet o_dataSet = null;
try
{
using (var conn = new NpgsqlConnection(PersistanceManager.GetConnectionString()))
{
conn.Open();
NpgsqlCommand comm = new NpgsqlCommand();
comm.CommandTimeout = 300;
foreach (var parameter in parameters)
{
comm.Parameters.Add(parameter);
}
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = sql;
comm.Connection = conn;
//var reader = comm.ExecuteReader();
NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(comm);
o_dataSet = new DataSet();
dataAdapter.Fill(o_dataSet);
//var dataTable = new DataTable();
//dataTable.Load(reader);
//while (reader.Read())
//{
// Console.Write("{0}\n", reader[0]);
//}
//o_dataSet.Tables.Add(dataTable);
conn.Close();
}
}
catch (Exception e)
{
Logger.Exception(e);
}
return(o_dataSet);
}
private void ReadUgovor(string brojPrijave, string kupac)
{
StaticFilters = new List <string>();
StaticFilters.Add("(broj like concat(@broj,'%') or @broj is null)");
StaticFilters.Add("(lower(kupac_naziv) like concat(lower(@kupac_ime),'%') or @kupac_ime is null)");
List <string> filters = new List <string>();
filters = filters.Concat(StaticFilters).ToList();
if (DynamicFilters != null)
{
filters = filters.Concat(DynamicFilters).ToList();
}
using (var conn = new NpgsqlConnection(PersistanceManager.GetConnectionString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
dgvPrijave.DataSource = null;
dgvPrijave.AutoGenerateColumns = false;
dgvPrijave.Columns.Clear();
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "R.broj", DataPropertyName = "broj", Width = 80
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Datum", DataPropertyName = "datum", Width = 100, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Šifra kupca", DataPropertyName = "kupac_sifra", Width = 50, Visible = false
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Kupac", DataPropertyName = "kupac_naziv", Width = 180
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Adresa", DataPropertyName = "kupac_adresa", Width = 180
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Telefon", DataPropertyName = "kupac_telefon", Width = 130
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Br.računa", DataPropertyName = "broj_racuna", Width = 130
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Iznos ugovora (sa PDV)", DataPropertyName = "iznos_sa_pdv", DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "N2"
}, Width = 120
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Inicijalno uplaćeno", DataPropertyName = "inicijalno_placeno", DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "N2"
}, Width = 120
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Uplaćeno po ratama", DataPropertyName = "uplaceno_po_ratama", DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "N2"
}, Width = 120
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Suma uplata", DataPropertyName = "suma_uplata", Width = 120, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "N2"
}
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Preostalo za uplatu", DataPropertyName = "preostalo_za_uplatu", DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "N2"
}, Width = 120
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Status", DataPropertyName = "status", Width = 80
});
// Retrieve all rows
cmd.Parameters.Clear();
Npgsql.NpgsqlParameter p1 = new NpgsqlParameter("@kupac_ime", DbType.String);
cmd.Parameters.Add(p1);
Npgsql.NpgsqlParameter p2 = new NpgsqlParameter("@broj", DbType.String);
cmd.Parameters.Add(p2);
if (kupac == "")
{
p1.Value = DBNull.Value;
}
else
{
p1.Value = kupac;
}
if (brojPrijave == "")
{
p2.Value = DBNull.Value;
}
else
{
p2.Value = brojPrijave;
}
cmd.CommandText = @"SELECT broj, datum, kupac_sifra, kupac_maticni_broj, kupac_broj_lk, kupac_naziv, kupac_adresa, kupac_telefon, broj_racuna, radnik, inicijalno_placeno, iznos_bez_pdv, pdv, iznos_sa_pdv, broj_rata, suma_uplata, preostalo_za_uplatu, status, napomena, mk,uplaceno_po_ratama
FROM public.ugovor" ;
if (filters.Count > 0)
{
cmd.CommandText += " WHERE ";
foreach (string f in filters)
{
cmd.CommandText += f + " AND ";
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 4);
}
cmd.CommandText += " order by datum desc";
using (var reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
dgvPrijave.DataSource = dt;
dgvPrijave.Refresh();
}
}
}
}
private void ReadPrijava(string brojPrijave, string kupac)
{
StaticFilters = new List <string>();
StaticFilters.Add("(broj like concat(@broj,'%') or @broj is null)");
StaticFilters.Add("(lower(kupac_ime) like concat('%',lower(@kupac_ime),'%') or (lower(predmet) like concat('%',lower(@kupac_ime),'%')) or @kupac_ime is null)");
List <string> filters = new List <string>();
filters = filters.Concat(StaticFilters).ToList();
if (DynamicFilters != null)
{
filters = filters.Concat(DynamicFilters).ToList();
}
using (var conn = new NpgsqlConnection(PersistanceManager.GetConnectionString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
dgvPrijave.DataSource = null;
dgvPrijave.AutoGenerateColumns = false;
dgvPrijave.Columns.Clear();
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "R.broj", DataPropertyName = "broj", Width = 80
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Datum prijema", DataPropertyName = "datum", Width = 100, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Šifra kupca", DataPropertyName = "kupac_sifra", Width = 50, Visible = false
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Kupac", DataPropertyName = "kupac_ime", Width = 180
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Adresa", DataPropertyName = "kupac_adresa", Width = 180
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Telefon", DataPropertyName = "kupac_telefon", Width = 130
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "E-mail", DataPropertyName = "kupac_email", Visible = false
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Predmet", DataPropertyName = "predmet", Width = 250
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Model", DataPropertyName = "model", Visible = true
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "SB", DataPropertyName = "serijski_broj", Visible = false
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Dodatna oprema", DataPropertyName = "dodatna_oprema", Visible = false
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Dobavljač", DataPropertyName = "dobavljac_sifra", Width = 80
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Email dobavljaču", DataPropertyName = "poslat_mejl_dobavljacu", Width = 100, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Datum vraćanja", DataPropertyName = "datum_vracanja", Width = 100, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Završeno", DataPropertyName = "zavrseno", Width = 100, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
// Retrieve all rows
cmd.Parameters.Clear();
Npgsql.NpgsqlParameter p1 = new NpgsqlParameter("@kupac_ime", DbType.String);
cmd.Parameters.Add(p1);
Npgsql.NpgsqlParameter p2 = new NpgsqlParameter("@broj", DbType.String);
cmd.Parameters.Add(p2);
if (kupac == "")
{
p1.Value = DBNull.Value;
}
else
{
p1.Value = kupac;
}
if (brojPrijave == "")
{
p2.Value = DBNull.Value;
}
else
{
p2.Value = brojPrijave;
}
cmd.CommandText = @"SELECT broj, broj_naloga, datum, kupac_sifra, kupac_ime, kupac_adresa, kupac_telefon, kupac_email, model, serijski_broj, dodatna_oprema, predmet, napomena_servisera, serviser, serviser_primio, zavrseno, dobavljac_sifra, dobavljac, datum_vracanja, poslat_mejl_dobavljacu, garantni_rok, broj_garantnog_lista, broj_racuna,instalacija_os,instalacija_office, instalacija_ostalo,instalacija
FROM prijava" ;
if (filters.Count > 0)
{
cmd.CommandText += " WHERE ";
foreach (string f in filters)
{
cmd.CommandText += f + " AND ";
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 4);
}
cmd.CommandText += " order by datum desc";
using (var reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
dgvPrijave.DataSource = dt;
dgvPrijave.Refresh();
}
}
}
}
private void ReadAll(string sifra, string naziv)
{
StaticFilters = new List <string>();
StaticFilters.Add("(sifra=@sifra or @sifra is null)");
StaticFilters.Add("((lower(naziv) like concat(lower(@naziv),'%') or @naziv is null))");
List <string> filters = new List <string>();
filters = filters.Concat(StaticFilters).ToList();
if (DynamicFilters != null)
{
filters = filters.Concat(DynamicFilters).ToList();
}
using (var conn = new NpgsqlConnection(PersistanceManager.GetConnectionString()))
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
dgvMain.DataSource = null;
dgvMain.AutoGenerateColumns = false;
dgvMain.Columns.Clear();
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Šifra", DataPropertyName = "sifra", Width = 80
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Tip", DataPropertyName = "tip", Width = 100
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Naziv", DataPropertyName = "naziv", Width = 180, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Matični broj", DataPropertyName = "maticni_broj", Width = 100
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Adresa", DataPropertyName = "adresa", Width = 300
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Telefon", DataPropertyName = "telefon", Width = 140
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Email", DataPropertyName = "email", Width = 160, DefaultCellStyle = new DataGridViewCellStyle()
{
Format = "dd.MM.yyyy."
}
});
dgvMain.Columns.Add(new DataGridViewCheckBoxColumn()
{
Name = "Kupac", DataPropertyName = "kupac", Width = 50
});
dgvMain.Columns.Add(new DataGridViewCheckBoxColumn()
{
Name = "Dobavljač", DataPropertyName = "dobavljac", Width = 75
});
dgvMain.Columns.Add(new DataGridViewTextBoxColumn()
{
Name = "Broj LK", DataPropertyName = "broj_lk", Width = 100, Visible = false
});
// Retrieve all rows
cmd.Parameters.Clear();
Npgsql.NpgsqlParameter p1 = new NpgsqlParameter("@naziv", DbType.String);
cmd.Parameters.Add(p1);
Npgsql.NpgsqlParameter p2 = new NpgsqlParameter("@sifra", DbType.Int32);
cmd.Parameters.Add(p2);
if (naziv == "")
{
p1.Value = DBNull.Value;
}
else
{
p1.Value = naziv;
}
if (sifra == "")
{
p2.Value = DBNull.Value;
}
else
{
p2.Value = sifra;
}
cmd.CommandText = @"SELECT sifra, naziv, tip, maticni_broj, adresa,
telefon, email,kupac,dobavljac,broj_lk FROM partner";
if (filters.Count > 0)
{
cmd.CommandText += " WHERE ";
foreach (string f in filters)
{
cmd.CommandText += f + " AND ";
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 4);
}
cmd.CommandText += " order by sifra asc";
using (var reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader);
dgvMain.DataSource = dt;
dgvMain.Refresh();
}
}
}
}