/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
/// <summary>
/// 执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
}
}
}
public async Task Get_update_command_infers_parameters_with_NpgsqDbType()
{
using var conn = await OpenConnectionAsync();
await using var _ = await GetTempTableName(conn, out var table);
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (
Cod varchar(5) NOT NULL,
Descr varchar(40),
Data date,
DataOra timestamp,
Intero smallInt NOT NULL,
Decimale money,
Singolo float,
Booleano bit,
Nota varchar(255),
BigIntArr bigint[],
VarCharArr character varying(20)[],
PRIMARY KEY (Cod)
);
INSERT INTO {table} VALUES('key1', 'description', '2018-07-03', '2018-07-03 07:02:00', 123, 123.4, 1234.5, B'1', 'note')");
var daDataAdapter =
new NpgsqlDataAdapter(
$"SELECT Cod, Descr, Data, DataOra, Intero, Decimale, Singolo, Booleano, Nota, BigIntArr, VarCharArr FROM {table}", conn);
var cbCommandBuilder = new NpgsqlCommandBuilder(daDataAdapter);
var dtTable = new DataTable();
daDataAdapter.InsertCommand = cbCommandBuilder.GetInsertCommand();
daDataAdapter.UpdateCommand = cbCommandBuilder.GetUpdateCommand();
daDataAdapter.DeleteCommand = cbCommandBuilder.GetDeleteCommand();
Assert.That(daDataAdapter.UpdateCommand.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[3].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[4].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[5].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[6].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[7].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[8].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[9].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[10].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[11].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[13].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[15].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Date));
Assert.That(daDataAdapter.UpdateCommand.Parameters[17].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Timestamp));
Assert.That(daDataAdapter.UpdateCommand.Parameters[18].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Smallint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[20].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Money));
Assert.That(daDataAdapter.UpdateCommand.Parameters[22].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Double));
Assert.That(daDataAdapter.UpdateCommand.Parameters[24].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Bit));
Assert.That(daDataAdapter.UpdateCommand.Parameters[26].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Varchar));
Assert.That(daDataAdapter.UpdateCommand.Parameters[28].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Bigint));
Assert.That(daDataAdapter.UpdateCommand.Parameters[30].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Array | NpgsqlDbType.Varchar));
daDataAdapter.Fill(dtTable);
var row = dtTable.Rows[0];
Assert.That(row[0], Is.EqualTo("key1"));
Assert.That(row[1], Is.EqualTo("description"));
Assert.That(row[2], Is.EqualTo(new DateTime(2018, 7, 3)));
Assert.That(row[3], Is.EqualTo(new DateTime(2018, 7, 3, 7, 2, 0)));
Assert.That(row[4], Is.EqualTo(123));
Assert.That(row[5], Is.EqualTo(123.4));
Assert.That(row[6], Is.EqualTo(1234.5));
Assert.That(row[7], Is.EqualTo(true));
Assert.That(row[8], Is.EqualTo("note"));
dtTable.Rows[0]["Singolo"] = 1.1D;
Assert.That(daDataAdapter.Update(dtTable), Is.EqualTo(1));
}
public InsEdit(string title, string name, string dbConnection)
{
InitializeComponent();
Title = title;
tableName = name;
dbConn = new NpgsqlConnection(dbConnection);
tmp = new DataSet();
dbConn.Open();
switch (name)
{
case "Conference":
{
label1.Content = "Название:";
label2.Content = "Дата начала:";
label3.Content = "Дата окончания:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
datePicker2.Visibility = Visibility.Visible;
break;
}
case "SectionLeaders":
{
label1.Content = "ФИО:";
label2.Content = "Кафедра:";
label3.Content = "Должность:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
textBox2.Visibility = Visibility.Visible;
textBox3.Visibility = Visibility.Visible;
break;
}
case "Section":
{
Height = 420;
button1.Margin = new Thickness(70, 350, 0, 0);
button2.Margin = new Thickness(209, 350, 0, 0);
label1.Content = "Название:";
label2.Content = "Дата начала работы:";
label3.Content = "Дата завершения:";
label4.Content = "Конференция:";
label5.Content = "Руководители секции:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
label5.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
datePicker2.Visibility = Visibility.Visible;
comboBox1.Margin = new Thickness(176, 232, 0, 0);
comboBox2.Margin = new Thickness(176, 295, 0, 0);
comboBox1.Visibility = Visibility.Visible;
comboBox2.Visibility = Visibility.Visible;
tmp.Tables.Add(new DataTable("Conference"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Conference", dbConn);
adapter.Fill(tmp.Tables["Conference"]);
tmp.Tables.Add(new DataTable("SectionLeaders"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM SECTION_LEADER", dbConn);
adapter.Fill(tmp.Tables["SectionLeaders"]);
comboBox1.ItemsSource = tmp.Tables["Conference"].DefaultView;
comboBox1.DisplayMemberPath = "name";
comboBox1.SelectedValuePath = "id";
comboBox2.ItemsSource = tmp.Tables["SectionLeaders"].DefaultView;
comboBox2.DisplayMemberPath = "name";
comboBox2.SelectedValuePath = "id";
break;
}
case "Member":
{
Height = 500;
button1.Margin = new Thickness(70, 400, 0, 0);
button2.Margin = new Thickness(209, 400, 0, 0);
label1.Content = "ФИО:";
label2.Content = "Место работы:";
label3.Content = "Должность:";
label4.Content = "Email:";
label5.Content = "Контактный телефон:";
label6.Content = "Адрес:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
label5.Visibility = Visibility.Visible;
label6.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
textBox2.Visibility = Visibility.Visible;
textBox3.Visibility = Visibility.Visible;
textBox4.Visibility = Visibility.Visible;
textBox5.Visibility = Visibility.Visible;
textBox6.Visibility = Visibility.Visible;
break;
}
case "Lecture":
{
Height = 350;
button1.Margin = new Thickness(70, 280, 0, 0);
button2.Margin = new Thickness(209, 280, 0, 0);
label1.Content = "Название доклада:";
label2.Content = "Дата выступления:";
label3.Content = "Секция:";
label4.Content = "Участник:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
comboBox1.Visibility = Visibility.Visible;
comboBox2.Visibility = Visibility.Visible;
comboBox1.Margin = new Thickness(176, 169, 0, 0);
comboBox2.Margin = new Thickness(176, 232, 0, 0);
tmp.Tables.Add(new DataTable("Section"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Section", dbConn);
adapter.Fill(tmp.Tables["Section"]);
tmp.Tables.Add(new DataTable("Member"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Member", dbConn);
adapter.Fill(tmp.Tables["Member"]);
comboBox1.ItemsSource = tmp.Tables["Section"].DefaultView;
comboBox1.DisplayMemberPath = "name";
comboBox1.SelectedValuePath = "id";
comboBox2.ItemsSource = tmp.Tables["Member"].DefaultView;
comboBox2.DisplayMemberPath = "name";
comboBox2.SelectedValuePath = "id";
break;
}
}
dbConn.Close();
}
private string GetDatasFromDb(string WoTitle, string query)
{
string Data = WoTitle + Environment.NewLine;
try
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
using (var conn = new NpgsqlConnection(connstring))
{
conn.Open();
var dataAdapter = new NpgsqlDataAdapter(query, conn);
ds.Reset();
dataAdapter.Fill(ds);
dt = ds.Tables[0];
}
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();
int i = 0;
foreach (var item in columnNames)
{
if (!item.Contains("pic") && !item.Contains("file"))
{
Data += "\t- " + item + ": " + dt.Rows[0][i].ToString() + Environment.NewLine;
}
else
{
if (dt.Rows[0][i].ToString().Contains("Byte") && item.Contains("file"))
{
//MessageBox.Show(dt.Rows[0][i].ToString()+" "+item);
Docs.Add((byte[])dt.Rows[0][i]);
}
if (item.Contains("filename"))
{
//MessageBox.Show("DOC "+WoTitle+" "+item+" "+ dt.Rows[0][i] +" "+i.ToString());
// its a file
Documentums.Text += Environment.NewLine + "- " + Convert.ToString(dt.Rows[0][i]);
}
else if (item.Contains("pic"))
{
// its an image
BitmapImage imgFromDb = LoadImage((byte[])dt.Rows[0][i]);
if (imgFromDb == null)
{
break; //break if there is no pic in byte array
}
System.Windows.Controls.Image img = new System.Windows.Controls.Image
{
Margin = new Thickness(18, 10, 18, 0),
Width = 200,
Height = 150,
ToolTip = WoTitle.Substring(3, WoTitle.Length - 4),
Source = imgFromDb
};
imgContainer.Children.Add(img);
if (imgContainer.Children.Count == 1)
{
Images.Children.Add(imgContainer);
}
if (imgContainer.Children.Count == 4)
{
imgContainer = new StackPanel {
Orientation = Orientation.Horizontal
}
}
;
//MessageBox.Show("kep"+WoTitle+" "+item+" "+ dt.Rows[0][i] +" "+i);
}
}
i++;
}
Data += Environment.NewLine;
}
catch (Exception)
{
return("");
}
return(Data);
}
private void buttonPesquisar_Click(object sender, EventArgs e)
{
if (maskedTextBoxPesquisa.Text == "")
{
MessageBox.Show("Preencha o campo de pesquisa", "Atenção!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else if (radioButtonNome.Checked)
{
try
{
string pesquisar = maskedTextBoxPesquisa.Text;
string lucas = pesquisar + "%";
//Populando dataGridView com informações do banco de dados
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;");
conn.Open();
NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE nome_manifestante ILIKE @nome_manifestante ORDER BY nome_manifestante", conn);
comando.Parameters.AddWithValue("@nome_manifestante", lucas);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando);
DataSet ds = new DataSet();
DataTable manifestante = new DataTable();
da.Fill(manifestante);
dataGridViewManifestante.DataSource = manifestante;
conn.Close();
}
catch
{
MessageBox.Show("Nome não localizado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else if (radioButtonCodigo.Checked)
{
try
{
string pesquisar = maskedTextBoxPesquisa.Text;
int codPesquisar = Convert.ToInt32(pesquisar);
//Populando dataGridView com informações do banco de dados
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;");
conn.Open();
NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE cod_manifestante = @cod_manifestante", conn);
comando.Parameters.AddWithValue("@cod_manifestante", codPesquisar);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando);
DataSet ds = new DataSet();
DataTable manifestante = new DataTable();
da.Fill(manifestante);
dataGridViewManifestante.DataSource = manifestante;
conn.Close();
}
catch
{
MessageBox.Show("Código não localizado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else if (radioButtonDocumento.Checked)
{
try
{
string pesquisar = maskedTextBoxPesquisa.Text;
//Populando dataGridView com informações do banco de dados
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;");
conn.Open();
NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE cpf_manifestante = @cpf_manifestante", conn);
comando.Parameters.AddWithValue("@cpf_manifestante", pesquisar);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando);
DataSet ds = new DataSet();
DataTable manifestante = new DataTable();
da.Fill(manifestante);
dataGridViewManifestante.DataSource = manifestante;
conn.Close();
}
catch
{
MessageBox.Show("CPF não localizado", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else if (radioButtonData.Checked)
{
try
{
string pesquisar = maskedTextBoxPesquisa.Text;
DateTime dataConvertida = Convert.ToDateTime(pesquisar);
//Populando dataGridView com informações do banco de dados
NpgsqlConnection conn = new NpgsqlConnection("Server=localhost; Port=5432; User Id=postgres; Password=@postgre; Database=ouvidoria;");
conn.Open();
NpgsqlCommand comando = new NpgsqlCommand("SELECT * FROM manifestante WHERE data_nasc_manifestante = @data_nasc_manifestante", conn);
comando.Parameters.AddWithValue("@data_nasc_manifestante", dataConvertida);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(comando);
DataSet ds = new DataSet();
DataTable manifestante = new DataTable();
da.Fill(manifestante);
dataGridViewManifestante.DataSource = manifestante;
conn.Close();
}
catch
{
MessageBox.Show("Data de nascimento não localizada", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
public static DataTable getConsultaAlugueis(NpgsqlConnection conexao, int campo, int tipo, String descricao)
{
DataTable dt = new DataTable();
try
{
String sql = "SELECT * FROM alugueis";
String nomeCampoOrdenacao = "i_pagamentos";
switch (campo)
{
case 0:
sql += " where cast (i_pagamentos as varchar(20)) ";
nomeCampoOrdenacao = "i_pagamentos";
break;
case 1:
sql += " where cast (i_imoveis as varchar(20)) ";
nomeCampoOrdenacao = "i_imoveis";
break;
case 2:
sql += " where cast (i_pessoas as varchar(20)) ";
nomeCampoOrdenacao = "i_pessoas";
break;
default:
sql += " where cast (i_alugueis as varchar(20)) ";
nomeCampoOrdenacao = "i_alugueis";
break;
}
switch (tipo)
{
case 0:
sql += " like '%" + descricao + "%'";
break;
case 1:
sql += " like '" + descricao + "%'";
break;
case 2:
sql += " like '%" + descricao + "'";
break;
case 3:
sql += " = '" + descricao + "'";
break;
case 4:
sql += " >= '" + descricao + "'";
break;
default:
sql += " <= '" + descricao + "'";
break;
}
sql += " order by " + nomeCampoOrdenacao;
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conexao;
cmd.CommandText = sql;
NpgsqlDataAdapter dat = new NpgsqlDataAdapter(cmd);
dat.Fill(dt);
}
catch (NpgsqlException erro)
{
MessageBox.Show("Erro de SQL:" + erro.Message);
Console.WriteLine("Erro de SQL:" + erro.Message);
}
return(dt);
}
private void ListBtn_Click(object sender, RoutedEventArgs e)
{
if (checkProductBtn.Visibility.ToString() == "Visible")
{
if ((sender as Button).Background.ToString() == "#FFADD8E6" || (sender as Button).Name.ToString() == "SaveBtn" || (sender as Button).Name.ToString().Contains("Tab"))
{
workStationCbx.SelectedIndex = workflowCbx.SelectedIndex;
}
else
{
workSteps.Remove("productQuantity");
workSteps.Add("productQuantity", "get_product_quantity(current_date,current_date)");
workStationCbx.SelectedIndex = workStationCbx.Items.Count - 1;
}
}
Int32 Tabcount = 0;
using (new WaitCursor())
{
try
{
using (var conn = new NpgsqlConnection(connstring))
{
conn.Open();
string sql = BuildSqlQueryCommand();
//MessageBox.Show(sql);
var dataAdapter = new NpgsqlDataAdapter(sql, conn);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
resultDataGrid.ItemsSource = dataTable.AsDataView();
//Hide byte [] array text where data is null
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i].ColumnName.Contains("pic"))
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
byte[] blob = (byte[])dataTable.Rows[j][i];
MemoryStream stream = new MemoryStream();
if (blob.Length > 10)
{
// change the "byte [] array" text to something more readable.
}
else
{
dataTable.Rows[j][i] = null;
}
}
}
}
// get the query result count
var countcmd = new NpgsqlCommand("SELECT COUNT(*)" + SqlCommandForCounter.Substring(8, SqlCommandForCounter.Length - 8), conn);
Tabcount = Convert.ToInt32(countcmd.ExecuteScalar());
resultRowCount.Content = Tabcount;
}
}
catch (Exception msg)
{
MessageBox.Show(msg.Message);
}
if (workStationCbx.SelectedValue.ToString() == "get_product_quantity(current_date,current_date)")
{
int sum = 0;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
sum += int.Parse(dataTable.Rows[i][2].ToString());
}
resultRowCount.Content = sum.ToString();
workFlow.Clear();
workFlow.Add("00 Transistor Dátumok", "TRANZISZTOR DÁTUM");
workFlow.Add("11 MB HS Szerelés", "MAINBOARD HEATSZINK SZERELÉS");
workFlow.Add("12 MB DSP Szerelés", "MAINBOARD DSP SZERELÉS");
workFlow.Add("21 FB ACDC Szerelés", "FILTERBOARD AC DC SZERELÉS");
workFlow.Add("22 FB EMC Szerelés", "FILTERBOARD EMC SHIELD SZERELÉS");
workFlow.Add("31 Ház Leak Teszt I.", "HÁZ LEAK TESZT");
workFlow.Add("32 Hűtőkör Leak Teszt", "COOLING LEAK TESZT");
workFlow.Add("33 Ház FB Szerelés", "HÁZ FILTERBOARD SZERELÉS");
workFlow.Add("34 Potting után Kapton", "POTTING UTÁN KAPTONOZÁS");
workFlow.Add("35 Ház Konnektor Szerelés", "HÁZ KONNEKTOR SZERELÉS");
workFlow.Add("41 Végszerelés I.", "VÉGSZERELÉS I.");
workFlow.Add("42 HiPot Teszt I.", "HIPOT I. GEN");
workFlow.Add("43 Kalibráció", "KALIBRÁCIÓ GEN");
workFlow.Add("44 Végszerelés II.", "VÉGSZERELÉS II.");
workFlow.Add("45 Leak Teszt II.", "LEAK TESZT VÉGSZERELÉS II. UTÁN");
workFlow.Add("46 Hipot Teszt II.", "HIPOT II.");
workFlow.Add("47 EOL", "EOL");
workFlow.Add("48 Firewall", "FIREWALL");
workflowCbx.ItemsSource = workFlow;
workflowCbx.DisplayMemberPath = "Value";
workflowCbx.SelectedValuePath = "Key";
workflowCbx.SelectedIndex = 0;
}
Tabs.Children.Clear();
for (int i = 0; i <= Tabcount / 200; i++)
{
Button newBtn = new Button
{
Background = Brushes.White,
Foreground = Brushes.DarkSlateGray,
BorderThickness = new Thickness(0),
Focusable = false
};
newBtn.Click += getOffset;
newBtn.Content = (i + 1).ToString();
newBtn.Name = "Tab" + (i + 1).ToString();
newBtn.Width = 34;
newBtn.Margin = new Thickness(1, 1, 1, 0);
newBtn.FontSize = 20;
Tabs.Children.Add(newBtn);
//change back and foreground color on active button
if (int.Parse(DbQueryOffset) == i * 200)
{
newBtn.Background = (SolidColorBrush) new BrushConverter().ConvertFrom("#FF008F95");
newBtn.Foreground = Brushes.White;
}
}
DbQueryOffset = "0";
}
}
protected override DataTable BuildSchemaTable(Boolean withGeometryColumn)
{
DataTable dt = null;
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
CollectionExpression <PropertyNameExpression> attributes = null;
if (DefaultProviderProperties != null)
{
attributes = GetProviderPropertyValue
<AttributesCollectionExpression, CollectionExpression <PropertyNameExpression> >(
DefaultProviderProperties.ProviderProperties,
null);
}
string columns = attributes == null
?
"*"
:
string.Join(",", Enumerable.ToArray(Processor.Select(attributes,
delegate(
PropertyNameExpression
o)
{
return
(QualifyColumnName
(
o.
PropertyName));
})));
if (columns != "*")
{
if (!columns.Contains(QualifyColumnName(GeometryColumn)))
{
columns = string.Format("{0},{1}", QualifyColumnName(GeometryColumn), columns);
}
if (!columns.Contains(QualifyColumnName(OidColumn)))
{
columns = string.Format("{0},{1}", QualifyColumnName(OidColumn), columns);
}
}
using (
NpgsqlCommand cmd =
new NpgsqlCommand(string.Format("SELECT {0} FROM {1} LIMIT 1;", columns, QualifiedTableName),
conn))
{
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
dt = ds.Tables["Table"];
}
conn.Close();
}
if (!dt.Columns.Contains("oid") && HasOids)
{
dt.Columns.Add(new DataColumn("oid", typeof(Int64)));
DataColumn dc = dt.Columns["oid"];
dc.SetOrdinal(0);
if (dt.Constraints.Count == 0)
{
dt.Constraints.Add("PK", dt.Columns[0], true);
}
}
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == GeometryColumn)
{
dt.Columns[i].DataType = typeof(Byte[]);
}
}
if (!withGeometryColumn)
{
dt.Columns.Remove(GeometryColumn);
}
//remove Primary Key to avoid possibliy mismatched PrimaryKey of FeatureDataTable
dt.PrimaryKey = null;
return(dt);
}
public List <string> TableScripts()
{
List <string> res = new List <string>();
NpgsqlConnection serverConnection = new NpgsqlConnection(ConnectString);
serverConnection.Open();
List <string> tables = new List <string>();
DataTable dt = serverConnection.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
string tablename = (string)row[2];
tables.Add(tablename);
// SystemLog.Write(tablename);
string SelectString = @"SELECT TOP 1 * FROM " + tablename;
NpgsqlDataAdapter serverAdapter = new NpgsqlDataAdapter();
serverAdapter.SelectCommand = new NpgsqlCommand(SelectString, serverConnection);
DataSet datasetServer = new DataSet();
serverAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
serverAdapter.Fill(datasetServer, tablename);
DataColumn[] primaryKeys = datasetServer.Tables[tablename].PrimaryKey;
string createTable = "CREATE TABLE " + tablename + "(";
DataColumnCollection columns = datasetServer.Tables[tablename].Columns;
int counter = 0;
foreach (DataColumn c in columns)
{
string type = "";
if (c.DataType == typeof(int))
{
type = "int";
}
else if (c.DataType == typeof(double))
{
type = "float";
}
else if (c.DataType == typeof(string))
{
type = "ntext";
}
else if (c.DataType == typeof(DateTime))
{
type = "datetime";
}
else if (c.DataType == typeof(Boolean))
{
type = "bit";
}
else
{
type = "UNKWON";
}
createTable += (counter == 0 ? "" : ",") + c.ToString() + " " + type;
counter++;
}
if (primaryKeys != null && primaryKeys.Length > 0)
{
createTable += ", PRIMARY KEY(";
counter = 0;
foreach (DataColumn c in primaryKeys)
{
createTable += (counter == 0 ? "" : ", ") + c.ToString();
counter++;
}
createTable += ")";
}
createTable += ")";
res.Add(createTable);
}
serverConnection.Close();
return(res);
}
public InsEdit(string title, string name, DataRow currentRow, string dbConnectionString)
{
InitializeComponent();
Title = title;
currentDataRow = currentRow;
tableName = name;
dbConn = new NpgsqlConnection(dbConnectionString);
tmp = new DataSet();
dbConn.Open();
switch (name)
{
case "Conference":
{
label1.Content = "Название:";
label2.Content = "Дата начала:";
label3.Content = "Дата окончания:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
datePicker2.Visibility = Visibility.Visible;
button1.Content = "Изменить";
textBox1.Text = currentRow["NAME"].ToString();
datePicker1.SelectedDate = DateTime.Parse(currentRow["OPENING_DATE"].ToString());
datePicker2.SelectedDate = DateTime.Parse(currentRow["CLOSING_DATE"].ToString());
break;
}
case "SectionLeaders":
{
label1.Content = "ФИО:";
label2.Content = "Кафедра:";
label3.Content = "Должность:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
textBox2.Visibility = Visibility.Visible;
textBox3.Visibility = Visibility.Visible;
button1.Content = "Изменить";
textBox1.Text = currentRow["NAME"].ToString();
textBox2.Text = currentRow["DEPARTMENT"].ToString();
textBox3.Text = currentRow["JOB_POSITION"].ToString();
break;
}
case "Section":
{
Height = 420;
button1.Margin = new Thickness(70, 350, 0, 0);
button2.Margin = new Thickness(209, 350, 0, 0);
label1.Content = "Название:";
label2.Content = "Дата начала работы:";
label3.Content = "Дата завершения:";
label4.Content = "Конференция:";
label5.Content = "Руководители секции:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
label5.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
datePicker2.Visibility = Visibility.Visible;
comboBox1.Margin = new Thickness(176, 232, 0, 0);
comboBox2.Margin = new Thickness(176, 295, 0, 0);
comboBox1.Visibility = Visibility.Visible;
comboBox2.Visibility = Visibility.Visible;
tmp.Tables.Add(new DataTable("Conference"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Conference", dbConn);
adapter.Fill(tmp.Tables["Conference"]);
tmp.Tables.Add(new DataTable("SectionLeaders"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM SECTION_LEADER", dbConn);
adapter.Fill(tmp.Tables["SectionLeaders"]);
comboBox1.ItemsSource = tmp.Tables["Conference"].DefaultView;
comboBox1.DisplayMemberPath = "name";
comboBox1.SelectedValuePath = "id";
comboBox1.SelectedValue = currentRow["ID_CONFERENCE"];
comboBox2.ItemsSource = tmp.Tables["SectionLeaders"].DefaultView;
comboBox2.DisplayMemberPath = "name";
comboBox2.SelectedValuePath = "id";
comboBox2.SelectedValue = currentRow["ID_EMPLOYEE"];
button1.Content = "Изменить";
textBox1.Text = currentRow["NAME"].ToString();
datePicker1.SelectedDate = DateTime.Parse(currentRow["OPENING_DATE"].ToString());
datePicker2.SelectedDate = DateTime.Parse(currentRow["CLOSING_DATE"].ToString());
break;
}
case "Member":
{
Height = 500;
button1.Margin = new Thickness(70, 400, 0, 0);
button2.Margin = new Thickness(209, 400, 0, 0);
label1.Content = "ФИО:";
label2.Content = "Место работы:";
label3.Content = "Должность:";
label4.Content = "Email:";
label5.Content = "Контактный телефон:";
label6.Content = "Адрес:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
label5.Visibility = Visibility.Visible;
label6.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
textBox2.Visibility = Visibility.Visible;
textBox3.Visibility = Visibility.Visible;
textBox4.Visibility = Visibility.Visible;
textBox5.Visibility = Visibility.Visible;
textBox6.Visibility = Visibility.Visible;
button1.Content = "Изменить";
textBox1.Text = currentRow["NAME"].ToString();
textBox2.Text = currentRow["JOB_PLACE"].ToString();
textBox3.Text = currentRow["JOB_POSITION"].ToString();
textBox4.Text = currentRow["EMAIL"].ToString();
textBox5.Text = currentRow["PHONE_NUMBER"].ToString();
textBox6.Text = currentRow["ADDRESS"].ToString();
break;
}
case "Lecture":
{
Height = 350;
button1.Margin = new Thickness(70, 280, 0, 0);
button2.Margin = new Thickness(209, 280, 0, 0);
label1.Content = "Название доклада:";
label2.Content = "Дата выступления:";
label3.Content = "Секция:";
label4.Content = "Участник:";
label1.Visibility = Visibility.Visible;
label2.Visibility = Visibility.Visible;
label3.Visibility = Visibility.Visible;
label4.Visibility = Visibility.Visible;
textBox1.Visibility = Visibility.Visible;
datePicker1.Visibility = Visibility.Visible;
comboBox1.Visibility = Visibility.Visible;
comboBox2.Visibility = Visibility.Visible;
comboBox1.Margin = new Thickness(176, 169, 0, 0);
comboBox2.Margin = new Thickness(176, 232, 0, 0);
tmp.Tables.Add(new DataTable("Section"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Section", dbConn);
adapter.Fill(tmp.Tables["Section"]);
tmp.Tables.Add(new DataTable("Member"));
adapter = new NpgsqlDataAdapter($"SELECT ID, Name FROM Member", dbConn);
adapter.Fill(tmp.Tables["Member"]);
comboBox1.ItemsSource = tmp.Tables["Section"].DefaultView;
comboBox1.DisplayMemberPath = "name";
comboBox1.SelectedValuePath = "id";
comboBox1.SelectedValue = currentRow["ID_SECTION"];
comboBox2.ItemsSource = tmp.Tables["Member"].DefaultView;
comboBox2.DisplayMemberPath = "name";
comboBox2.SelectedValuePath = "id";
comboBox2.SelectedValue = currentRow["ID_MEMBER"];
button1.Content = "Изменить";
textBox1.Text = currentRow["NAME"].ToString();
datePicker1.SelectedDate = DateTime.Parse(currentRow["PERFORMANCE_DATE"].ToString());
break;
}
}
dbConn.Close();
}
/// <summary>
/// Execute a query.
/// </summary>
/// <param name="query">Database query defined outside of the database client.</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Query(string query)
{
if (String.IsNullOrEmpty(query))
{
throw new ArgumentNullException(query);
}
DataTable result = new DataTable();
if (DebugRawQuery)
{
Console.WriteLine("RawQuery: " + query);
}
switch (_DbType)
{
case DbTypes.MsSql:
#region Mssql
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
sda.Fill(result);
conn.Dispose();
conn.Close();
}
break;
#endregion
case DbTypes.MySql:
#region Mysql
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = query;
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds != null)
{
if (ds.Tables != null)
{
if (ds.Tables.Count > 0)
{
result = ds.Tables[0];
}
}
}
conn.Close();
}
break;
#endregion
case DbTypes.PgSql:
#region Pgsql
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
{
result = ds.Tables[0];
}
conn.Close();
}
break;
#endregion
}
if (DebugResultRowCount)
{
if (result != null)
{
Console.WriteLine("RawQuery: returning " + result.Rows.Count + " row(s)");
}
else
{
Console.WriteLine("RawQery: returning null");
}
}
return(result);
}
public List <DomainModel.RecommenderAction> GetWeeklyActivities()
{
List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select userid, productid from viewedproducts where time>@t union select userid, productid from viewedproducts where " +
"time>=@tp and time<=@tk";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddDays(-21)));
if (DateTime.Now.Month >= 3 && DateTime.Now.Month <= 5)
{
cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 3, 1)));
cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 5, 31)));
}
else if (DateTime.Now.Month >= 6 && DateTime.Now.Month <= 8)
{
cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 6, 1)));
cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 8, 31)));
}
else if (DateTime.Now.Month >= 9 && DateTime.Now.Month <= 11)
{
cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 9, 1)));
cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 11, 30)));
}
else
{
cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 2, 12, 1)));
cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 2, 28)));
}
da = new NpgsqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "View";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t union select userid, productid, grade from reviewedproducts where " +
"time>=@tp and time<=@tk";
dt.Clear();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "Review";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
action.Grade = int.Parse(dr["grade"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid from boughtproducts where time>@t union select userid, productid from boughtproducts where " +
"time>=@tp and time<=@tk";
dt.Clear();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "Buy";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid from seenreviews where time>@t union select userid, productid from seenreviews where " +
"time>=@tp and time<=@tk";
dt.Clear();
da.Fill(dt);
MongodbFunctions mongo = new MongodbFunctions();
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "SeeReviews";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
action.Grade = mongo.AverageGrade(action.ProductId)[0];
actions.Add(action);
}
cmd.Dispose();
return(actions);
}
public List <DomainModel.RecommenderAction> GetMonthlyActivities(int months)
{
List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandText = "select userid, productid from viewedproducts where time>@t";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddMonths(-1 * months)));
da = new NpgsqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "View";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t";
dt.Clear();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "Review";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
action.Grade = int.Parse(dr["grade"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid from boughtproducts where time>@t";
dt.Clear();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "Buy";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
actions.Add(action);
}
cmd.CommandText = "select userid, productid from seenreviews where time>@t";
dt.Clear();
da.Fill(dt);
MongodbFunctions mongo = new MongodbFunctions();
foreach (DataRow dr in dt.Rows)
{
DomainModel.RecommenderAction action = new DomainModel.RecommenderAction();
action.Action = "SeeReviews";
action.UserId = new ObjectId(dr["userid"].ToString());
action.ProductId = new ObjectId(dr["productid"].ToString());
action.Grade = mongo.AverageGrade(action.ProductId)[0];
actions.Add(action);
}
cmd.Dispose();
return(actions);
}
public string Get(string tkn, string argsr)
{
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
DataTable dt = new DataTable();
DataTable cdt = new DataTable("cshttl");
DataTable cddt = new DataTable("cshdet");;
DataSet argsds;
DataSet csrpds = new DataSet();
string rst = "";
if (argsr == null)
{
return("error");
}
try {
argsds = JsonConvert.DeserializeObject <DataSet>(argsr);
}
catch (System.Exception ex)
{
_logger.LogError("error : Ctrl-CshRpt,mthd-Get. Error parse args: {errmsg}", ex.Message);
return("error: " + ex.Message);
}
NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder();
var cnf = _configuration["DBConn:DBHost"];
csb.Host = cnf.ToString();
csb.Port = 6432;
csb.Username = "******";
csb.Database = "micro";
csb.CommandTimeout = 1200;
csb.MaxPoolSize = 500;
csb.Timeout = 180;
cnf = _configuration["DBConn:DBPass"];
csb.Password = cnf.ToString();
string fst = csb.ConnectionString;
conn = new NpgsqlConnection(fst);
conn.Open();
NpgsqlCommand nsc = new NpgsqlCommand("SELECT * FROM users WHERE name=split_part('" + tkn + "',':',1) AND pass=split_part('" + tkn + "',':',2)", conn);
rst = "";
da.SelectCommand = nsc;
da.Fill(dt);
if (dt.Rows.Count > 0)
{
if (((bool)dt.Rows[0]["super"]) || ((bool)dt.Rows[0]["boss"]) || (Convert.ToInt32(dt.Rows[0]["id"]) == 921))
{
nsc = new NpgsqlCommand("SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') ORDER BY cdate desc,posid", conn);
da.SelectCommand = nsc;
da.Fill(cdt);
nsc = new NpgsqlCommand("select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "', (SELECT array_agg(id) FROM poses)) order by pid", conn);
da.SelectCommand = nsc;
da.Fill(cddt);
}
else
{
if (((bool)dt.Rows[0]["posadm"]))
{
nsc = new NpgsqlCommand("set session datestyle to 'Euro,DMY'; SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') WHERE posid IN (" + dt.Rows[0]["psls"].ToString() + ") ORDER BY cdate desc,posid", conn);
da.SelectCommand = nsc;
da.Fill(cdt);
nsc = new NpgsqlCommand("set session datestyle to 'Euro,DMY'; select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "',array[" + dt.Rows[0]["psls"].ToString() + "]) order by pid", conn);
da.SelectCommand = nsc;
da.Fill(cddt);
}
else
{
nsc = new NpgsqlCommand("SELECT * FROM vw_dt_cash('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "') WHERE posid =" + dt.Rows[0]["posid"].ToString() + " ORDER BY cdate desc,posid", conn);
da.SelectCommand = nsc;
da.Fill(cdt);
nsc = new NpgsqlCommand("select * from get_cshr_det('" + Convert.ToDateTime(argsds.Tables[0].Rows[0]["bdt"]).ToShortDateString() + "','" +
Convert.ToDateTime(argsds.Tables[0].Rows[0]["edt"]).ToShortDateString() + "',array[" + dt.Rows[0]["posid"].ToString() + "]) order by pid", conn);
da.SelectCommand = nsc;
da.Fill(cddt);
}
}
CultureInfo current = CultureInfo.CurrentCulture;
CultureInfo frc;
frc = new CultureInfo("ru-RU");
csrpds.Tables.AddRange(new DataTable[] { cdt, cddt });
JsonSerializerSettings microsoftDateFormatSettings = new JsonSerializerSettings
{
DateFormatHandling = DateFormatHandling.IsoDateFormat
};
rst = JsonConvert.SerializeObject(csrpds, new IsoDateTimeConverter {
DateTimeFormat = "dd.MM.yyyy"
});
_logger.LogInformation("Method CshRpt-Get {tkn}. Success", tkn.Split(':')[0]);
}
else
{
_logger.LogInformation("Method CshRpt-Get {tkn}. No such sucker", tkn.Split(':')[0]);
rst = "error";
}
conn.Close();
return(rst);
}
static public Library.Cliente FindById(long idCliente)
{
NpgsqlConnection conexao = null;
NpgsqlDataAdapter dap = null;
DataSet ds = null;
Library.Cliente cliente = null;
try
{
conexao = new NpgsqlConnection(global::Connection.Connection.StringPostgree());
dap = new NpgsqlDataAdapter("SELECT * FROM \"Cliente\" WHERE id='" + idCliente + "'", conexao);
ds = new DataSet();
dap.Fill(ds, "Cliente");
if (ds.Tables["Cliente"].Rows.Count == 1)
{
DateTime date = DateTime.MinValue;
DateTime.TryParse(ds.Tables["Cliente"].Rows[0]["nascimento"].ToString(), out date);
DateTime dataCadastro = DateTime.MinValue;
DateTime.TryParse(ds.Tables["Cliente"].Rows[0]["dataCadastro"].ToString(), out dataCadastro);
cliente = new Cliente();
cliente.Id = (long)ds.Tables["Cliente"].Rows[0]["id"];
cliente.Bairro = ds.Tables["Cliente"].Rows[0]["bairro"].ToString();
cliente.Celular = ds.Tables["Cliente"].Rows[0]["celular"].ToString();
cliente.Cep = ds.Tables["Cliente"].Rows[0]["cep"].ToString();
cliente.Cpf = ds.Tables["Cliente"].Rows[0]["cpf"].ToString();
cliente.Cnpj = ds.Tables["Cliente"].Rows[0]["cnpj"].ToString();
cliente.Cidade = ds.Tables["Cliente"].Rows[0]["cidade"].ToString();
cliente.Email = ds.Tables["Cliente"].Rows[0]["email"].ToString();
cliente.Endereco = ds.Tables["Cliente"].Rows[0]["endereco"].ToString();
cliente.EnderecoNumero = ds.Tables["Cliente"].Rows[0]["enderecoNumero"].ToString();
cliente.Estado = ds.Tables["Cliente"].Rows[0]["estado"].ToString();
cliente.Fax = ds.Tables["Cliente"].Rows[0]["fax"].ToString();
cliente.InscricaoEstadual = ds.Tables["Cliente"].Rows[0]["inscricaoEstadual"].ToString();
cliente.Nascimento = date;
cliente.Nome = ds.Tables["Cliente"].Rows[0]["nome"].ToString();
cliente.NomeContato = ds.Tables["Cliente"].Rows[0]["nomeContato"].ToString();
cliente.NomeFantasia = ds.Tables["Cliente"].Rows[0]["nomeFantasia"].ToString();
cliente.Observacoes = ds.Tables["Cliente"].Rows[0]["observacoes"].ToString();
cliente.Pessoa = ds.Tables["Cliente"].Rows[0]["pessoa"].ToString();
cliente.Rg = ds.Tables["Cliente"].Rows[0]["Rg"].ToString();
cliente.ReferenciaComercial = ds.Tables["Cliente"].Rows[0]["referenciaComercial"].ToString();
cliente.Site = ds.Tables["Cliente"].Rows[0]["site"].ToString();
cliente.Telefone = ds.Tables["Cliente"].Rows[0]["telefone"].ToString();
cliente.DataCadastro = dataCadastro;
return(cliente);
}
else
{
return(null);
}
}
catch (Exception ex)
{
Library.Classes.Logger.Error(ex);;
}
finally
{
conexao.Close();
dap.Dispose();
ds.Dispose();
}
return(null);
}
public List <Seller> readAll(NpgsqlConnection conn)
{
List <Seller> result = new List <Seller>();
string sql = "SELECT dim_sellers.seller_id,sms,ap_id,agent_code,agent_name,email,dim_sellers.cellphone,weekly_goal, " +
"id_empresa ,sum(fact_sales.sold_week) AS sold_week ,sum(fact_sales.sold_month) AS sold_month, sum(fact_sales.sold_today) AS sold_day "+
"FROM dim_sellers LEFT JOIN fact_sales ON dim_sellers.seller_id = fact_sales.seller_id WHERE sms = true " +
"GROUP BY dim_sellers.seller_id,sms,ap_id,agent_code,agent_name,email,dim_sellers.cellphone,weekly_goal,id_empresa";
DataTable dt = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Seller seller = new Seller();
seller.ID = long.Parse(dt.Rows[i][0].ToString());
seller.SMS = bool.Parse(dt.Rows[i][1].ToString());
seller.AP_ID = long.Parse(dt.Rows[i][2].ToString());
seller.Code = dt.Rows[i][3].ToString();
seller.Name = dt.Rows[i][4].ToString();
seller.Email = dt.Rows[i][5].ToString();
seller.CellPhone = dt.Rows[i][6].ToString();
seller.WeeklyGoal = float.Parse(dt.Rows[i][7].ToString());
seller.Enterprise_ID = long.Parse(dt.Rows[i][8].ToString());
if (dt.Rows[i][9].ToString() == "")
{
seller.CumplimientoSemana = 0;
}
else
{
seller.CumplimientoSemana = float.Parse(dt.Rows[i][9].ToString());
}
if (dt.Rows[i][10].ToString() == "")
{
seller.CumplimientoMensual = 0;
}
else
{
seller.CumplimientoMensual = float.Parse(dt.Rows[i][10].ToString());
}
if (dt.Rows[i][11].ToString() == "")
{
seller.CumplimientoDiario = 0;
}
else
{
seller.CumplimientoDiario = float.Parse(dt.Rows[i][11].ToString());
}
result.Add(seller);
}
}
da.Dispose();
listSellers = result;
return(result);
}
override internal void LoadForTable()
{
NpgsqlConnection cn = null;
try
{
string query = "select * from information_schema.columns where table_catalog = '" +
this.Table.Database.Name + "' and table_schema = '" + this.Table.Schema +
"' and table_name = '" + this.Table.Name + "' order by ordinal_position";
cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
DataTable metaData = new DataTable();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn);
adapter.Fill(metaData);
metaData.Columns["udt_name"].ColumnName = "TYPE_NAME";
metaData.Columns["data_type"].ColumnName = "TYPE_NAMECOMPLETE";
if (metaData.Columns.Contains("TYPE_NAME"))
{
f_TypeName = metaData.Columns["TYPE_NAME"];
}
if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
{
f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
}
PopulateArray(metaData);
// IsAutoKey logic
// query = "SELECT a.attname AS column_name, c2.relname AS seq_name " +
// "FROM pg_class c1, pg_class c2, pg_namespace n, pg_depend d, pg_attribute a " +
// "WHERE n.nspname = '" + this.Table.Schema + "' AND c1.relname = '" + this.Table.Name + "' " +
// "AND c2.relkind = 'S' AND c1.relnamespace = n.oid " +
// "AND d.refobjid = c1.oid AND c2.oid = d.objid " +
// "AND a.attrelid = c1.oid AND d.refobjsubid = a.attnum";
query = @"SELECT a.attname AS column_name, substring(pg_get_expr(ad.adbin, c.oid) " +
@"FROM '[\'""]+(.+?)[\'""]+') AS seq_name " +
"FROM pg_class c, pg_namespace n, pg_attribute a, pg_attrdef ad " +
"WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " +
"AND c.relnamespace = n.oid " +
"AND a.attrelid = c.oid AND a.atthasdef = true " +
"AND ad.adrelid = c.oid AND ad.adnum = a.attnum " +
@"AND pg_get_expr(ad.adbin, c.oid) LIKE 'nextval(%'";
DataTable seqData = new DataTable();
adapter = new NpgsqlDataAdapter(query, cn);
adapter.Fill(seqData);
DataRowCollection rows = seqData.Rows;
if (rows.Count > 0)
{
string colName;
for (int i = 0; i < rows.Count; i++)
{
colName = rows[i]["column_name"] as string;
PostgreSQL8Column col = this[colName] as PostgreSQL8Column;
col._isAutoKey = true;
query = "SELECT min_value, increment_by FROM \"" + rows[i]["seq_name"] + "\"";
adapter = new NpgsqlDataAdapter(query, cn);
DataTable autokeyData = new DataTable();
adapter.Fill(autokeyData);
Int64 a;
a = (Int64)autokeyData.Rows[0]["min_value"];
col._autoInc = Convert.ToInt32(a);
a = (Int64)autokeyData.Rows[0]["increment_by"];
col._autoSeed = Convert.ToInt32(a);
}
}
cn.Close();
}
catch
{
if (cn != null)
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
DataSet dataSet = new DataSet();
string sqlString = "Server = localhost; Port = 5432; User Id = postgres; Password = drazen2814mia; Database = postgres;";
NpgsqlConnection remoteConnection = new NpgsqlConnection(sqlString);
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
NpgsqlDataAdapter da = new NpgsqlDataAdapter("CREATE DATABASE db" + DateTime.Now.Year.ToString() + " WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'Croatian_Croatia.1250' LC_CTYPE = 'Croatian_Croatia.1250' CONNECTION LIMIT = -1", remoteConnection);
dataSet.Reset();
da.Fill(dataSet);
remoteConnection.Close();
string sql = "";
string br = "";
string data_type = "";
DataTable DT = classSQL.select_settings("select table_name from information_schema.tables where TABLE_TYPE <> 'VIEW'", "Table").Tables[0];
//dataGridView1.DataSource = DT;
for (int i = 0; i < DT.Rows.Count; i++)
{
DataTable DTkey = classSQL.select_settings("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='" + DT.Rows[i]["TABLE_NAME"].ToString() + "'", "INFORMATION_SCHEMA.KEY_COLUMN_USAGE").Tables[0];
DataTable DTT = classSQL.select_settings("SELECT AUTOINC_INCREMENT,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION FROM information_schema.columns WHERE TABLE_NAME ='" + DT.Rows[i]["TABLE_NAME"].ToString() + "'", "Table").Tables[0];
sql += "CREATE TABLE " + DT.Rows[i]["TABLE_NAME"].ToString() + "(\n";
for (int y = 0; y < DTT.Rows.Count; y++)
{
if (DTT.Rows[y]["CHARACTER_MAXIMUM_LENGTH"].ToString() != "" && DTT.Rows[y]["DATA_TYPE"].ToString() != "ntext")
{
br = "(" + DTT.Rows[y]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
}
else
{
br = "";
}
if (DTT.Rows[y]["DATA_TYPE"].ToString() == "int")
{
data_type = "integer";
}
else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "nvarchar")
{
data_type = "character varying";
}
else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "datetime")
{
data_type = "timestamp without time zone";
}
else if (DTT.Rows[y]["DATA_TYPE"].ToString() == "ntext")
{
data_type = "text";
}
else
{
data_type = DTT.Rows[y]["DATA_TYPE"].ToString();
}
if (DTT.Rows[y]["AUTOINC_INCREMENT"].ToString() == "1")
{
if (DTkey.Rows.Count > 0)
{
AutoNumber = DTkey.Rows[0]["COLUMN_NAME"].ToString();
data_type = "serial";
}
}
sql += DTT.Rows[y]["COLUMN_NAME"].ToString() + " " + data_type + br + ",\n";
}
if (DTkey.Rows.Count > 0)
{
if (DTkey.Rows[0]["COLUMN_NAME"].ToString() != "")
{
sql += "CONSTRAINT " + DT.Rows[i]["TABLE_NAME"].ToString() + "_primary_key" + " PRIMARY KEY (" + DTkey.Rows[0]["COLUMN_NAME"].ToString() + "),\n";
}
}
sql = sql.Remove(sql.Length - 2, 2);
sql += ")\n\n";
sqlString = "Server = localhost; Port = 5432; User Id = postgres; Password = drazen2814mia; Database = db" + DateTime.Now.Year.ToString() + ";";
//sqlString = SQL.claasConnectDatabase.GetRemoteConnectionString();
remoteConnection = new NpgsqlConnection(sqlString);
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
da = new NpgsqlDataAdapter(sql, remoteConnection);
//richTextBox1.Text = sql.ToString();
dataSet.Reset();
da.Fill(dataSet);
remoteConnection.Close();
sql = "";
DataTable DTcompact = classSQL.select_settings("SELECT * FROM " + DT.Rows[i]["TABLE_NAME"].ToString(), DT.Rows[i]["TABLE_NAME"].ToString()).Tables[0];
//CREATE DATABASE newdb WITH TEMPLATE originaldb;
for (int r = 0; r < DTcompact.Rows.Count; r++)
{
string columnName = "";
string values = "";
sql = "INSERT INTO " + DT.Rows[i]["TABLE_NAME"].ToString() + " ";
for (int ii = 0; ii < DTcompact.Columns.Count; ii++)
{
if (AutoNumber != DTcompact.Columns[ii].ToString())
{
columnName += DTcompact.Columns[ii].ToString() + ",";
values += "'" + DTcompact.Rows[r][DTcompact.Columns[ii]].ToString().Replace("'", "") + "'" + ",";
values = values.Replace("\"", "");
values = values.Replace("[", "");
values = values.Replace("]", "");
values = values.Replace("(", "");
values = values.Replace(")", "");
values = values.Replace("\\", "");
}
else
{
AutoNumber = "";
}
}
columnName = columnName.Remove(columnName.Length - 1, 1);
values = values.Remove(values.Length - 1, 1);
sql += " ( " + columnName + " ) " + " VALUES " + " ( " + values + " ) ";
remoteConnection = new NpgsqlConnection(sqlString);
if (remoteConnection.State.ToString() == "Closed")
{
remoteConnection.Open();
}
da = new NpgsqlDataAdapter(sql, remoteConnection);
dataSet.Reset();
da.Fill(dataSet);
remoteConnection.Close();
sql = "";
}
}
MessageBox.Show("Spremljeno");
}
public void Start(string requestId, bool viewSQL)
{
try
{
if (Settings.Count == 0)
{
throw new Exception(CommonMessages.Exception_MissingSettings);
}
status.Code = RequestStatus.StatusCode.InProgress;
string server = Settings.GetAsString("Server", "");
string port = Settings.GetAsString("Port", "");
string userId = Settings.GetAsString("UserID", "");
string password = Settings.GetAsString("Password", "");
string database = Settings.GetAsString("Database", "");
string dataSourceName = Settings.GetAsString("DataSourceName", "");
string connectionTimeout = Settings.GetAsString("ConnectionTimeout", "15");
string commandTimeout = Settings.GetAsString("CommandTimeout", "120");
log.Debug("Connection timeout: " + connectionTimeout + ", Command timeout: " + commandTimeout);
if (!Settings.ContainsKey("DataProvider"))
{
throw new Exception(CommonMessages.Exception_MissingDataProviderType);
}
string connectionString = string.Empty;
switch ((Lpp.Dns.DataMart.Model.Settings.SQLProvider)Enum.Parse(typeof(Lpp.Dns.DataMart.Model.Settings.SQLProvider), Settings.GetAsString("DataProvider", ""), true))
{
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.ODBC:
if (string.IsNullOrWhiteSpace(dataSourceName))
{
throw new Exception(CommonMessages.Exception_MissingODBCDatasourceName);
}
using (OdbcConnection cn = new OdbcConnection(string.Format("DSN={0}", dataSourceName)))
try
{
OdbcDataAdapter da = new OdbcDataAdapter(query, cn);
da.Fill(resultDataset);
}
finally
{
cn.Close();
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.PostgreSQL:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port == null || port == string.Empty)
{
port = "5432";
}
connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
// Making connection with Npgsql provider
using (NpgsqlConnection connnection = new NpgsqlConnection(connectionString))
{
try
{
connnection.Open();
NpgsqlCommand command = new NpgsqlCommand(query, connnection);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connnection.Close();
}
}
break;
case Lpp.Dns.DataMart.Model.Settings.SQLProvider.SQLServer:
if (string.IsNullOrEmpty(server))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseServer);
}
if (string.IsNullOrEmpty(database))
{
throw new Exception(CommonMessages.Exception_MissingDatabaseName);
}
if (!string.IsNullOrEmpty(userId) && string.IsNullOrEmpty(password))
{
throw new Exception(CommonMessages.Exception_MissingDatabasePassword);
}
if (port != null && port != string.Empty)
{
server += ", " + port;
}
connectionString = userId != null && userId != string.Empty ? String.Format("server={0};User ID={1};Password={2};Database={3}; Connection Timeout={4}", server, userId, password, database, connectionTimeout) :
String.Format("server={0};integrated security=True;Database={1}; Connection Timeout={2}", server, database, connectionTimeout);
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
try
{
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection);
command.CommandTimeout = int.Parse(commandTimeout);
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
break;
//case Lpp.Dns.DataMart.Model.Settings.SQLProvider.Oracle:
// // TODO: Implement this provider
// //throw new NotImplementedException("Oracle client not implemented yet");
// //connectionString = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};;Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimeout, commandTimeout);
// //// TODO: Upgrade Oracle client
// //using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionString))
// //{
// // try
// // {
// // connection.Open();
// // System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(query, connection);
// // System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(command);
// // resultDataset.Reset();
// // da.Fill(resultDataset);
// // }
// // catch (Exception ex)
// // {
// // throw ex;
// // }
// // finally
// // {
// // connection.Close();
// // }
// //}
// break;
default:
throw new Exception(CommonMessages.Exception_InvalidDataProviderType);
}
status.Code = RequestStatus.StatusCode.Complete;
status.Message = "";
}
catch (Exception e)
{
status.Code = RequestStatus.StatusCode.Error;
status.Message = e.Message;
throw e;
}
}
private PostgresDatabase()
{
_adapter = new NpgsqlDataAdapter();
_cmd_builder = new NpgsqlCommandBuilder();
}
protected void refreshImageList()
{
//used to re-load images based on either clicking left or right arrow
//-9 for back arrow, +9 for forward arrow; if position < 9, make left arrow unclickable; if dt.Rows.Count
//if (given number from string (NOT TOTAL ROW NUMBER)) % 9 != 0), make right arrow unclickable
String LocalPhotoId, LocalTitle, LocalFilename, LocalAuthor, LocalUser, LocalPrice, LocalDate, LocalCollege, LocalDescription;
//String stmt = "SELECT photoid, title, filename FROM photo limit 9 offset " + offSetNum.ToString();
String stmt = "SELECT photoid, title, filename, author, \"user\".username, price, \"dateSubmitted\", college, description FROM photo left join \"user\" ON \"user\".userid = photo.userid ORDER BY photoid DESC limit 9 offset " + Session["offSetNum"].ToString();
int counter = 0;
String[] photoIdArray = new string[9];
String[] titleArray = new string[9];
fileNArray = new String[9];
String[] authorArray = new string[9];
String[] userArray = new string[9];
String[] priceArray = new string[9];
String[] dateArray = new string[9];
String[] collegeArray = new string[9];
String[] descArray = new string[9];
using (NpgsqlConnection conn2 = new NpgsqlConnection(connString))
{
using (NpgsqlCommand comm2 = new NpgsqlCommand())
{
comm2.Connection = conn2;
comm2.CommandType = CommandType.Text;
comm2.CommandText = stmt;
try
{
conn2.Open();
NpgsqlDataAdapter nsda = new NpgsqlDataAdapter(comm2);
nsda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow row in dt.Rows)
{
if (counter > 8)
{
counter = 0;
Array.Clear(photoIdArray, 0, photoIdArray.Length);
Array.Clear(titleArray, 0, titleArray.Length);
Array.Clear(fileNArray, 0, fileNArray.Length);
Array.Clear(authorArray, 0, authorArray.Length);
Array.Clear(userArray, 0, userArray.Length);
Array.Clear(priceArray, 0, priceArray.Length);
Array.Clear(dateArray, 0, dateArray.Length);
Array.Clear(collegeArray, 0, collegeArray.Length);
Array.Clear(descArray, 0, descArray.Length);
}
LocalPhotoId = "";
LocalTitle = "";
LocalFilename = "";
LocalAuthor = "";
LocalUser = "";
LocalPrice = "";
LocalDate = "";
LocalCollege = "";
LocalDescription = "";
foreach (DataColumn col in dt.Columns)
{
switch (col.Caption)
{
case "photoid":
LocalPhotoId = row[col].ToString();
break;
case "title":
LocalTitle = row[col].ToString();
break;
case "filename":
LocalFilename = row[col].ToString();
break;
case "author":
LocalAuthor = "Author: " + row[col].ToString();
break;
case "username":
LocalUser = "******" + row[col].ToString();
break;
case "price":
LocalPrice = row[col].ToString();
if (LocalPrice.Contains("."))
{
LocalPrice = "Price: $" + string.Format("{0:G29}", decimal.Parse(LocalPrice));
}
else
{
if (!String.IsNullOrEmpty(LocalPrice))
{
LocalPrice = "Price: $" + LocalPrice;
}
else
{
LocalPrice = "Price: $0";
}
}
break;
case "dateSubmitted":
LocalDate = "Date Posted: " + row[col].ToString();
break;
case "college":
LocalCollege = "University: " + row[col].ToString();
break;
case "description":
LocalDescription = "Description: " + row[col].ToString();
break;
default: break;
}
}
photoIdArray[counter] = LocalPhotoId;
titleArray[counter] = LocalTitle;
fileNArray[counter] = LocalFilename;
authorArray[counter] = LocalAuthor;
userArray[counter] = LocalUser;
priceArray[counter] = LocalPrice;
dateArray[counter] = LocalDate;
collegeArray[counter] = LocalCollege;
descArray[counter] = LocalDescription;
counter++;
}
imageDisplayTitle1.Text = titleArray[0];
imageDisplayTitle2.Text = titleArray[1];
imageDisplayTitle3.Text = titleArray[2];
imageDisplayTitle4.Text = titleArray[3];
imageDisplayTitle5.Text = titleArray[4];
imageDisplayTitle6.Text = titleArray[5];
imageDisplayTitle7.Text = titleArray[6];
imageDisplayTitle8.Text = titleArray[7];
imageDisplayTitle9.Text = titleArray[8];
imageDisplay1Price.Text = priceArray[0];
imageDisplay2Price.Text = priceArray[1];
imageDisplay3Price.Text = priceArray[2];
imageDisplay4Price.Text = priceArray[3];
imageDisplay5Price.Text = priceArray[4];
imageDisplay6Price.Text = priceArray[5];
imageDisplay7Price.Text = priceArray[6];
imageDisplay8Price.Text = priceArray[7];
imageDisplay9Price.Text = priceArray[8];
imageDisplay1Description.Text = descArray[0];
imageDisplay2Description.Text = descArray[1];
imageDisplay3Description.Text = descArray[2];
imageDisplay4Description.Text = descArray[3];
imageDisplay5Description.Text = descArray[4];
imageDisplay6Description.Text = descArray[5];
imageDisplay7Description.Text = descArray[6];
imageDisplay8Description.Text = descArray[7];
imageDisplay9Description.Text = descArray[8];
imageDisplay1Date.Text = dateArray[0];
imageDisplay2Date.Text = dateArray[1];
imageDisplay3Date.Text = dateArray[2];
imageDisplay4Date.Text = dateArray[3];
imageDisplay5Date.Text = dateArray[4];
imageDisplay6Date.Text = dateArray[5];
imageDisplay7Date.Text = dateArray[6];
imageDisplay8Date.Text = dateArray[7];
imageDisplay9Date.Text = dateArray[8];
imageDisplay1College.Text = collegeArray[0];
imageDisplay2College.Text = collegeArray[1];
imageDisplay3College.Text = collegeArray[2];
imageDisplay4College.Text = collegeArray[3];
imageDisplay5College.Text = collegeArray[4];
imageDisplay6College.Text = collegeArray[5];
imageDisplay7College.Text = collegeArray[6];
imageDisplay8College.Text = collegeArray[7];
imageDisplay9College.Text = collegeArray[8];
imageDisplay1User.Text = userArray[0];
imageDisplay2User.Text = userArray[1];
imageDisplay3User.Text = userArray[2];
imageDisplay4User.Text = userArray[3];
imageDisplay5User.Text = userArray[4];
imageDisplay6User.Text = userArray[5];
imageDisplay7User.Text = userArray[6];
imageDisplay8User.Text = userArray[7];
imageDisplay9User.Text = userArray[8];
imageDisplay1Author.Text = authorArray[0];
imageDisplay2Author.Text = authorArray[1];
imageDisplay3Author.Text = authorArray[2];
imageDisplay4Author.Text = authorArray[3];
imageDisplay5Author.Text = authorArray[4];
imageDisplay6Author.Text = authorArray[5];
imageDisplay7Author.Text = authorArray[6];
imageDisplay8Author.Text = authorArray[7];
imageDisplay9Author.Text = authorArray[8];
for (int k = 0; k < 9; k++)
{
displayImage(k);
}
/*
* String image1 = filePath + fileNArray[0].ToString();
* String image2 = filePath + fileNArray[1].ToString();
* String image3 = filePath + fileNArray[2].ToString();
* String image4 = filePath + fileNArray[3].ToString();
* String image5 = filePath + fileNArray[4].ToString();
* String image6 = filePath + fileNArray[5].ToString();
* String image7 = filePath + fileNArray[6].ToString();
* String image8 = filePath + fileNArray[7].ToString();
* String image9 = filePath + fileNArray[8].ToString();
*
* ImageDisplay1.ImageUrl = image1;
* ImageDisplay2.ImageUrl = image2;
* ImageDisplay3.ImageUrl = image3;
* ImageDisplay4.ImageUrl = image4;
* ImageDisplay5.ImageUrl = image5;
* ImageDisplay6.ImageUrl = image6;
* ImageDisplay7.ImageUrl = image7;
* ImageDisplay8.ImageUrl = image8;
* ImageDisplay9.ImageUrl = image9;
*/
}
dt.Clear();
nsda.Dispose();
}
catch (NpgsqlException ex)
{
}
}
conn2.Close();
}
CloseConn();
}
/// <summary>
/// This method will load all records for this investor that exist on our Projects_Entrepreneur_Investor_XREF table, because records on that table are what model this relationship.
/// </summary>
private void load_PROCS()
{
// make query to get a dataset from our Data Base
string query = "SELECT PIX.proc_investor_xref_id, PM.proc_master_id, PIX.create_date_time, PIX.investor_accepted, PM.proc_begin_date, PM.proc_end_date, PM.profit_percentage, PM.project_accepted FROM proc_investor_xref PIX JOIN proc_master PM ON PIX.proc_master_id = PM.proc_master_id WHERE PIX.investor_master_id = @InvestorID";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, conn);
da.SelectCommand.Parameters.AddWithValue("InvestorID", InvestorID);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
// if our query resturned no rows we should probably inform the user instead of making an empty table
try
{
if (ds.Tables[0].Rows.Count < 0)
{
PROCResults = "<h3>No PROCS were found for your account... perhaps you should find some projects and start investing</h3>";
}
else // start building what we will render onto the page by placing an open table bracket and table head to be shown
{
PROCResults += "<h3>Projects</h3><br />";
PROCResults += "<table id='tableProjectAccounts' class='table table-hover table-striped table-condensed'>";
PROCResults += "<thead><tr><th>Date Created</th><th>% of net Profit</th><th>Project Accepted</th><th>Investor Accepted</th><th>Begin Date</th><th>End Date</th></tr></thead>";
PROCResults += "<tbody>";
foreach (DataRow i in ds.Tables[0].Rows)
{
PROCResults += "<tr>";
PROCResults += "<td><a href='" + "ViewPROC/" + i["proc_master_id"].ToString() + "'>" + i["create_date_time"].ToString() + "</a></td>";
PROCResults += "<td>" + i["profit_percentage"].ToString() + "</td>";
try {
if ((bool)i["project_accepted"])
{
PROCResults += "<td>Accepted</td>";
}
else
{
PROCResults += "<td>Denied</td>";
}
}
catch (InvalidCastException e)
{
PROCResults += "<td>Denied</td>";
}
try
{
if ((bool)i["investor_accepted"])
{
PROCResults += "<td>Accepted</td>";
}
else
{
PROCResults += "<td>Denied</td>";
}
}
catch (InvalidCastException e)
{
PROCResults += "<td>Denied</td>";
}
PROCResults += "<td>" + i["proc_begin_date"].ToString() + "</td>";
PROCResults += "<td>" + i["proc_end_date"].ToString() + "</td>";
PROCResults += "</tr>";
}
PROCResults += "</tbody>";
PROCResults += "</table>";
}
}
catch (Exception e)
{
PROCResults = "<h3><Something went wrong attempting to get PROC tied to your profile!/h3>";
}
// loop over results set of our query for each row add a row to our html table
// after loop, now add the closing table tags
}
private void DoSomthing(object nobject)
{
try
{
NameObject nameObject = nobject as NameObject;
string quanpin = nameObject.quanpin;
string shouZim = nameObject.shouZim;
string hanzi = nameObject.hanzi;
string tableName = nameObject.tableName;
string msg2 = "开始获取所有数据信息";
if (this.OnProcessNotify != null)
{
this.OnProcessNotify(msg2, 0);
}
string sqlString = "SELECT gid," + hanzi + "," + quanpin + "," + shouZim + " FROM " + tableName + " where " + hanzi + " is not null";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sqlString, dbcon);
da.UpdateCommand = new NpgsqlCommand();
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("@" + quanpin, DbType.String, 254, quanpin));
da.UpdateCommand.Parameters.Add(new NpgsqlParameter("@" + shouZim, DbType.String, 254, shouZim));
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da);
DataSet dataset = new DataSet();
da.Fill(dataset);
msg2 = "完成获取所有数据信息";
if (this.OnProcessNotify != null)
{
this.OnProcessNotify(msg2, 0);
}
DataColumn hanziCol = dataset.Tables[0].Columns[hanzi];
DataColumn quanpinCol = dataset.Tables[0].Columns[quanpin];
DataColumn shouZimCol = dataset.Tables[0].Columns[shouZim];
int j = 0;
int count = dataset.Tables[0].Rows.Count;
foreach (DataRow row in dataset.Tables[0].Rows)
{
j++;
string sql = "";
try
{
string hanziValue = row[hanziCol].ToString().Trim();
row[quanpinCol] = Hz2Py.GetPinyin(hanziValue);
row[shouZimCol] = Hz2Py.GetFirstPinyin(hanziValue);
sql = "update " + tableName + " set " + quanpin + "='" + row[quanpinCol].ToString() + "'," + shouZim + "='" + row[shouZimCol] + "' where gid=" + row[0].ToString();
NpgsqlCommand objCommand = new NpgsqlCommand(sql, dbcon);
objCommand.ExecuteNonQuery();
}
catch
{
LogManager.writeLog("异常：<" + sql + ">语法错误");
}
string msg = "已处理路网数据" + j.ToString() + "条,共" + count.ToString() + "条";
if (this.OnProcessNotify != null)
{
this.OnProcessNotify(msg, (j * 100) / count);
}
}
string msg1 = "处理完成";
if (this.OnProcessNotify != null)
{
this.OnProcessNotify(msg1, 100);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void get_SearchResults(string arg)
{
// Define string array to hold both of our queries
string[] queries = new string[2];
/// <SQL> Easy to read sql string
/// SELECT EM.entrepreneur_master_id, EM.entrepreneur_profile_name, IMGM.image_file
/// FROM entrepreneur_master EM
/// LEFT OUTER JOIN entrepreneur_image_xref EMXREF
/// ON EMXREF.entrepreneur_master_id = EM.entrepreneur_master_id
/// LEFT OUTER JOIN image_master IMGM
/// ON IMGM.image_master_id = EMXREF.image_master_id
/// WHERE EM.entrepreneur_profile_name LIKE @arg
/// AND EM.entrepreneur_public = TRUE
/// </SQL>
queries[0] = "SELECT EM.entrepreneur_master_id, EM.entrepreneur_profile_name, IMGM.image_file FROM entrepreneur_master EM LEFT OUTER JOIN entrepreneur_image_xref EMXREF ON EMXREF.entrepreneur_master_id = EM.entrepreneur_master_id LEFT OUTER JOIN image_master IMGM ON IMGM.image_master_id = EMXREF.image_master_id WHERE EM.entrepreneur_profile_name LIKE @arg AND EM.entrepreneur_public = TRUE";
/// <SQL>
/// SELECT project_master_id, project_description
/// FROM project_master
/// WHERE project_description LIKE @arg
/// </SQL>
queries[1] = "SELECT project_master_id, project_description FROM project_master WHERE project_description LIKE @arg";
NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(queries[0], conn);
da1.SelectCommand.Parameters.AddWithValue("@arg", "%" + arg + "%");
DataSet ds1 = new DataSet();
try
{
conn.Open();
da1.Fill(ds1);
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
NpgsqlDataAdapter da2 = new NpgsqlDataAdapter(queries[1], conn);
da2.SelectCommand.Parameters.AddWithValue("@arg", string.Format("%{0}%", arg));
DataSet ds2 = new DataSet();
try
{
conn.Open();
da2.Fill(ds2);
}
catch (Exception e)
{
}
finally
{
conn.Close();
}
DataSet ds = new DataSet();
ds.Tables.Add(ds1.Tables[0].Copy());
ds2.Tables[0].TableName = "Table1";
ds.Tables.Add(ds2.Tables[0].Copy());
SearchDS = ds;
}
//Save to Excel
private void SaveBtn_Click(object sender, RoutedEventArgs e)
{
String resultat = "";
String result = "";
using (var conn = new NpgsqlConnection(connstring))
{
conn.Open();
for (int i = 0; i < Tabs.Children.Count; i++)
{
//fill datagrid
string sql = BuildSqlQueryCommand();
var dataAdapter = new NpgsqlDataAdapter(sql, conn);
dataSet.Reset();
dataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
resultDataGrid.ItemsSource = dataTable.AsDataView();
DbQueryOffset = ((i + 1) * 200).ToString();
// concatenate the tabs
resultDataGrid.SelectAllCells();
if (i == 0)
{
resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
}
else
{
resultDataGrid.ClipboardCopyMode = DataGridClipboardCopyMode.ExcludeHeader;
}
ApplicationCommands.Copy.Execute(null, resultDataGrid);
resultat += (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
result += (string)Clipboard.GetData(DataFormats.Text);
resultDataGrid.UnselectAllCells();
// set back the default result
if (i == Tabs.Children.Count - 1)
{
foreach (var item in Tabs.Children)
{
if ((item as Button).Foreground == Brushes.White)
{
DbQueryOffset = (int.Parse((item as Button).Content.ToString()) * 200 - 200).ToString();
}
}
ListBtn_Click(sender, e as RoutedEventArgs);
}
}
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel |*.xls";
//saveFileDialog.DefaultExt = "xls";
//saveFileDialog.AddExtension = true;
if (saveFileDialog.ShowDialog() == true)
{
StreamWriter file1 = new StreamWriter(saveFileDialog.FileName);
file1.WriteLine(result.Replace(',', ' '));
file1.Close();
}
}
public static string GetInfo(string target, int port)
{
string toReturn = "";
// Thanks Metasploit!
List <string> userList = new List <string>()
{
"postgres", "scott", "admin"
};
List <string> passList = new List <string>()
{
"tiger", "postgres", "password", "admin"
};
bool toBreak = false;
foreach (string username in userList)
{
if (toBreak)
{
break;
}
foreach (string password in passList)
{
if (toBreak)
{
break;
}
using (NpgsqlConnection conn = new NpgsqlConnection($"Host={target};Port={port};User Id={username};Password={password};Database=template1;"))
{
try
{
// Try connect
conn.Open();
// We're connect - Creds are correct!
toReturn += "- Login creds found: " + username + ":" + password + Environment.NewLine;
toBreak = true;
// Pull version info
try
{
NpgsqlCommand cmd = new NpgsqlCommand("SELECT version()", conn);
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
foreach (DataRow r in ds.Tables[0].Rows)
{
toReturn += "- Version: " + r["version"].ToString() + Environment.NewLine;
}
}
catch (Exception ex)
{
toReturn += "- Unable to pull Version info - Bug Reelix: " + ex.Message + Environment.NewLine;
}
try
{
NpgsqlCommand cmd = new NpgsqlCommand("SELECT usename, passwd, usesuper FROM pg_shadow", conn);
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
foreach (DataRow r in ds.Tables[0].Rows)
{
string dbUser = r["usename"].ToString();
string dbPass = r["passwd"].ToString();
bool superUser = bool.Parse(r["usesuper"].ToString());
toReturn += "- pg_shadow User: "******" - " + dbPass + " - Super User: "******"-- If a pg_shadow user password is cracked, you can get a shell with: use exploit/multi/postgres/postgres_copy_from_program_cmd_exec";
}
catch (Exception ex)
{
toReturn += "- Unable to pull pg_shadow info - Bug Reelix: " + ex.Message + Environment.NewLine;
}
}
catch (Exception ex)
{
if (ex.Message.Trim().StartsWith("28P01"))
{
// Invalid Password
}
else if (ex.Message == "Dependency unixODBC with minimum version 2.3.1 is required")
{
toReturn += "- Dependency missing. Download from: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17";
toBreak = true;
}
else
{
toReturn += "- Fatal Error in PostgreSQL.GetInfo - Bug Reelix: " + ex.Message;
if (ex.InnerException != null)
{
Console.WriteLine("Inner exception is not null: " + ex.InnerException.Message);
}
toBreak = true;
}
}
}
}
}
if (toReturn == "")
{
toReturn = "- Unable to find credentials";
}
return(toReturn.Trim(Environment.NewLine.ToCharArray()));
}
private void btnAceptar_Click(object sender, EventArgs e)
{
//Verifico que existan productos en la lista de venta
if (dataGridView2.Rows.Count > 0)
{
//Capturo los datos para la insercion en tabla venta
string fecha = dateTimePicker1.Value.ToShortDateString();
string comprobante = lblComprobante.Text;
//Realizo insercion en tabla venta
try
{
conn.Open();
string query = "INSERT INTO venta(comprobante,fecha, id_usuario) values(";
query += "'" + comprobante + "'";
query += ",'" + fecha + "'";
query += "," + id_usuario + ")";
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
if (cmd.ExecuteNonQuery() > 0)
{
//Si ya se registro la venta ahora registro el detalle de la venta
//Primero obtengo el id del ultimo registro en la tabla venta
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT * FROM venta ORDER BY id DESC LIMIT 1;", conn);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
DataRow dataRow = dataTable.Rows[0];
string idVenta = dataRow.ItemArray[0].ToString();
//Realizo la insercion por cada fila en la lista de venta
foreach (DataGridViewRow row in dataGridView2.Rows)
{
//Capturamos los datos
string id_producto = row.Cells[0].Value.ToString();
string cantidad_producto = row.Cells[4].Value.ToString();
string precio_producto = row.Cells[3].Value.ToString().Replace(',', '.');
//Realizamos insercion
query = "INSERT INTO detalle_venta(id_venta,id_producto,cantidad,precio) VALUES(";
query += idVenta + ",";
query += id_producto + ",";
query += cantidad_producto + ",";
query += precio_producto + ")";
NpgsqlCommand command = new NpgsqlCommand(query, conn);
if (command.ExecuteNonQuery() <= 0)
{
MessageBox.Show("Error al guardar detalle de compra");
}
}
//Cuando toda la transaccion se completo de forma exitosa muestro menaje y limpio componentes
MessageBox.Show("Venta registrada");
DataTable dtaux = dataGridView2.DataSource as DataTable;
if (dtaux != null)
{
dtaux.Rows.Clear();
}
lblComprobante.Text = obtenerComprobante();
}
else
{
MessageBox.Show("Error al registrar venta");
}
conn.Close();
//Actualizamos lista de productos disponibles
cargarInventario();
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
else
{
MessageBox.Show("Aun no ha agregado productos a la lista de venta");
}
}
public Ver_empleados(Ejecutor exec)
{
InitializeComponent();
ejec = exec;
sda = new NpgsqlDataAdapter();
}
private void LPSELoad(string type)
{
NpgsqlConnection pgConnection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString);
string ssql = string.Empty;
string hardCodeHandler = string.Empty;
if (type == "ANGGOTA")
{
if (UserName == "197005231997031003")
{
hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19700523199703100%' ";
}
else if (UserName == "197505272006041015")
{
hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19750527200604101%' ";
}
else if (UserName == "197904192008011003")
{
hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%19790419200801100%' ";
}
else if (UserName == "198210032009122002")
{
hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%198210032009022002%' ";
}
else
{
hardCodeHandler = "and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%"+ UserName + "%' ";
}
ssql = "select distinct paket.pkt_nama Paket, tahap.thp_nama Tahapan, jadwal.dtj_tglawal Awal, jadwal.dtj_tglakhir Akhir " +
//"peg_nama Nama, ltrim(rtrim(replace(peg_nip, ' ', ''))) NIP " +
"from jadwal " +
"inner join tahap on tahap.thp_id = jadwal.thp_id " +
"inner join lelang_seleksi on lelang_seleksi.lls_id = jadwal.lls_id and lelang_seleksi.lls_status = 1 " +
"inner join paket on paket.pkt_id = lelang_seleksi.pkt_id and paket.pkt_status = 1 " +
"inner join paket_panitia on paket.pkt_id = paket_panitia.pkt_id " +
"inner join anggota_panitia on anggota_panitia.pnt_id = paket_panitia.pnt_id " +
"inner join pegawai on pegawai.peg_id = anggota_panitia.peg_id " +
"where 1=1 " +
"and date_part('day', jadwal.dtj_tglakhir) = date_part('day', now()) AND date_part('month', jadwal.dtj_tglakhir) = date_part('month', now()) AND date_part('year', jadwal.dtj_tglakhir) = date_part('year', now()) " +
"and jadwal.lls_id = lelang_seleksi.lls_id " + hardCodeHandler +
"order by jadwal.dtj_tglawal, jadwal.dtj_tglakhir";
//jadwal.lls_id,
}
else
{
ssql = "select distinct paket.pkt_nama Paket, tahap.thp_nama Tahapan, jadwal.dtj_tglawal Awal, jadwal.dtj_tglakhir Akhir " +
//"peg_nama Nama, ltrim(rtrim(replace(peg_nip, ' ', ''))) NIP " +
"from jadwal " +
"inner join tahap on tahap.thp_id = jadwal.thp_id " +
"inner join lelang_seleksi on lelang_seleksi.lls_id = jadwal.lls_id and lelang_seleksi.lls_status = 1 " +
"inner join paket on paket.pkt_id = lelang_seleksi.pkt_id and paket.pkt_status = 1 " +
"inner join paket_panitia on paket.pkt_id = paket_panitia.pkt_id " +
"inner join anggota_panitia on anggota_panitia.pnt_id = paket_panitia.pnt_id " +
"inner join pegawai on pegawai.peg_id = anggota_panitia.peg_id " +
"where 1=1 " +
"and date_part('day', jadwal.dtj_tglakhir) = date_part('day', now()) AND date_part('month', jadwal.dtj_tglakhir) = date_part('month', now()) AND date_part('year', jadwal.dtj_tglakhir) = date_part('year', now()) " +
"and jadwal.lls_id = lelang_seleksi.lls_id " +
//"and ltrim(rtrim(replace(replace(peg_nip, ' ', ''),' ',''))) like '%" + UserName + "%' " +
"order by jadwal.dtj_tglawal, jadwal.dtj_tglakhir";
//jadwal.lls_id,
}
//NpgsqlCommand myCommand = new NpgsqlCommand();
//myCommand.CommandText = ssql;
//myCommand.CommandType = CommandType.Text;
//myCommand.Connection = pgConnection;
try
{
pgConnection.Open();
//NpgsqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
/*
* while (myReader.Read())
* {
*
* }
*/
dset = new DataSet("npdata");
NpAdapter = new NpgsqlDataAdapter();
NpAdapter.SelectCommand = new NpgsqlCommand(ssql, pgConnection);
NpAdapter.Fill(dset, "npdata");
dtsource = dset.Tables["npdata"];
gridJadwal.DataSource = dtsource;
gridJadwal.DataBind();
//gridJadwal.SetDataBinding(dset, "npdata");
//myReader.Close();
lpseStatusLabel.Text = "Tersambung";
pgParameterLabel.Text = ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString.Substring(0, 20);
}
catch (Exception ex)
{
lpseStatusLabel.Text = "Terputus" + " (Pesan Kesalahan: " + ex.Message + ")";
pgParameterLabel.Text = ConfigurationManager.ConnectionStrings["PGConnectionString"].ConnectionString;
}
}
private void progressTimer_Tick(object sender, EventArgs e)
{
if (!isTaskStart)
{
isTaskStart = true;
sw = System.Diagnostics.Stopwatch.StartNew();
Task exportTk = Task.Run(() =>
{
NpgsqlConnection lclConn = null;
try
{
lclConn = table.Owner.Conn.CloneWith(table.Owner.Conn.ConnectionString);
using (StreamWriter sw = new StreamWriter(fileName))
{
string hdr = "";
for (int i = 1; i < table.AssociatiedForm.MainDataGrid.ColumnCount; i++)
{
hdr += table.AssociatiedForm.MainDataGrid.Columns[i].Name + ", ";
}
hdr = hdr.Substring(0, hdr.Length - 2);
sw.WriteLine(hdr);
for (int pageId = 1; pageId <= table.NumPages; pageId++)
{
string strSql = String.Format(@"SELECT * FROM {0} ORDER BY {1} {2} LIMIT {3} OFFSET {4}", table.Name, table.OrderBy, table.Order, TableManager.RECORDS_PER_PAGE, (pageId - 1) * TableManager.RECORDS_PER_PAGE);
try
{
NpgsqlDataAdapter lclDa = new NpgsqlDataAdapter(strSql, lclConn);
DataSet lclDs = new DataSet();
lclDa.Fill(lclDs);
DataTable lclDt = lclDs.Tables[0];
for (int recId = 0; recId < lclDt.Rows.Count; recId++)
{
string v = "";
for (int i = 1; i < lclDt.Columns.Count; i++)
{
v += lclDt.Rows[recId][i] + ", ";
}
v = v.Substring(0, v.Length - 2);
sw.WriteLine(v);
}
Interlocked.Increment(ref pageExported);
}
catch (Exception exe)
{
lclConn.Open();
}
}
}
}
catch (Exception exc)
{
progressTimer.Stop();
MessageBox.Show(exc.Message);
if (lclConn != null)
{
lclConn.Close();
}
table.IsBeingExported = false;
isTaskStart = false;
Close();
}
});
}
lblProgress.Text = String.Format(@"{0} page(s) exported / {1} page(s) in total", pageExported, table.NumPages);
if (pageExported == table.NumPages)
{
progressTimer.Stop();
sw.Stop();
MessageBox.Show(String.Format(@"Table exported successfully! [Total Time Used {0}s]", (double)sw.ElapsedMilliseconds / 1000.0));
table.IsBeingExported = false;
isTaskStart = false;
Close();
}
}
void connection(String a)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
connection.Open();
String chart_select = "SELECT title FROM movie WHERE id = " + a;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(chart_select, connection);
connection.Close();
IMDb imdb = new IMDb("The Godfather", true);
String tittl = imdb.Id;
Label1.Text = Label1.Text + imdb.Title;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
return;
}
filterdiv.Visible = false;
var ds = new DataSet();
var dt = new DataTable();
// för filtrering
var medarbetare = new List <Person>();
var avdelningar = new List <string>();
string connstr = ConfigurationManager.ConnectionStrings["postgres connection"].ConnectionString;
using (var conn = new NpgsqlConnection(connstr))
{
conn.Open();
var sqlquery = "select avtal.id, avtalstyp, enligt_avtal, diarienummer, startdate, enddate, status, motpartstyp, sbk_avtal.ansvarig_avd.namn as ansvarig_avd, avtalstecknare.first_name || ' ' || avtalstecknare.last_name as \"Avtalstecknare\", ansvarig_sbk.first_name || ' ' || ansvarig_sbk.last_name as \"ansvarig_sbk\", avtalskontakt.first_name || ' ' || avtalskontakt.last_name as \"Avtalskontakt\", ansvarigsbk.first_name as \"Ansvarig SBK - Förnamn\", ansvarigsbk.last_name as \"Ansvarig SBK - efternamn\", ansvarigsbk.epost as \"Ansvarig SBK - epost\", data.first_name as \"datakontakt förnamn\", data.last_name as \"datakontakt efternamn\", data.epost as \"datakontakt epost\", kontakt.last_name as \"Avtalskontakt efternamn\", orgnummer, (select string_agg(beskrivning, ', ') from sbk_avtal.avtalsinnehall left join sbk_avtal.map_avtal_innehall on map_avtal_innehall.avtalsinnehall_id = avtalsinnehall.id where avtal.id = map_avtal_innehall.avtal_id) as avtalsinnehall from sbk_avtal.avtal left join sbk_avtal.person data on data.id=sbk_avtal.avtal.datakontakt left join sbk_avtal.person kontakt on kontakt.id=sbk_avtal.avtal.avtalskontakt left join sbk_avtal.person ansvarigsbk on ansvarigsbk.id=sbk_avtal.avtal.ansvarig_sbk left join sbk_avtal.person avtalstecknare on avtalstecknare.id=sbk_avtal.avtal.avtalstecknare left join sbk_avtal.person avtalskontakt on avtalskontakt.id=sbk_avtal.avtal.avtalskontakt left join sbk_avtal.person ansvarig_sbk on ansvarig_sbk.id=sbk_avtal.avtal.ansvarig_sbk left join sbk_avtal.ansvarig_avd on sbk_avtal.ansvarig_avd.id = sbk_avtal.avtal.ansvarig_avd;";
using (var da = new NpgsqlDataAdapter(sqlquery, conn))
{
da.Fill(ds);
}
var avdelningsquery = "select namn from sbk_avtal.ansvarig_avd;";
using (var cmd = new NpgsqlCommand(avdelningsquery, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
avdelningar.Add(reader.GetString(0));
}
}
}
var medarbetarequery = "select person.id, person.first_name, person.last_name from sbk_avtal.medarbetare_sbk left join sbk_avtal.person on sbk_avtal.medarbetare_sbk.person_id = sbk_avtal.person.id;";
using (var cmd = new NpgsqlCommand(medarbetarequery, conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
medarbetare.Add(new Person
{
id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
});
}
}
}
}
dt = ds.Tables[0];
// filtrerar bort inaktiva avtal från AvtalTableDataSource, men behåller dem i "OriginalDataSource
Session.Add("OriginalDataSource", dt);
dt = dt.AsEnumerable()
.Where(row => row.Field <string>("status") == "Aktivt")
.CopyToDataTable();
Session.Add("AvtalTableDataSource", dt);
AvtalTable.DataSource = dt;
AvtalTable.DataBind();
// filtrerings-rullistor
avdelningdd.Items.Add("Alla avdelningar");
medarbetaredd.Items.Add("Alla");
foreach (var avd in avdelningar)
{
avdelningdd.Items.Add(avd);
}
for (int i = 0; i < medarbetare.Count; i++)
{
var person = medarbetare[i];
person.dropdownindex = i + 1;
medarbetaredd.Items.Add(string.Format("{0} {1}", person.FirstName, person.LastName));
}
//Session.Add("AvtalTableDataSource", dt);
Session.Add("medarbetare", medarbetare);
Session.Add("avdelningar", avdelningar);
}
