public void SelectDistinctShelve(PhpJSON php)
{
List <Record> shelveZaznam = new List <Record>();
string selectShelve = "SELECT DISTINCT place_shelve FROM kteem_record";
try
{
php.localDatabase.con = new SQLiteConnection("Data Source=" + php.localDatabase.dbFile);
php.localDatabase.con.Open();
php.localDatabase.cmd = php.localDatabase.con.CreateCommand();
php.localDatabase.cmd.CommandText = selectShelve;
php.localDatabase.reader = php.localDatabase.cmd.ExecuteReader();
php.cmbBoxShelve.Items.Add(" ");
while (php.localDatabase.reader.Read())
{
shelveZaznam.Add(new Record()
{
place_shelve = php.localDatabase.reader[0].ToString()
});
php.cmbBoxShelve.Items.Add(php.localDatabase.reader[0]);
}
php.localDatabase.reader.Close();
php.localDatabase.cmd.ExecuteNonQuery();
php.localDatabase.con.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void ViewSqliteToDb(PhpJSON php)
{
con = new SQLiteConnection("Data Source= " + dbFile);
adapter = new SQLiteDataAdapter();
string CommandText = "SELECT id, meta_sap, data_name, place_room_sap, place_locker, place_shelve FROM kteem_record";
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = CommandText;
cmd.ExecuteNonQuery();
//adapter = new SQLiteDataAdapter(CommandText, con);
//using (dt = new DataTable())
//{
// adapter.Fill(dt);
// php.dataGridView1.DataSource = dt;
//}
using (SQLiteDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
php.dataGridView1.Rows.Add(new object[] {
read.GetValue(0), // U can use column index
read.GetValue(read.GetOrdinal("id")), // Or column name like this
read.GetValue(read.GetOrdinal("meta_sap")),
read.GetValue(read.GetOrdinal("data_name")),
read.GetValue(read.GetOrdinal("place_room_sap")),
read.GetValue(read.GetOrdinal("place_shelve")),
});
}
}
con.Close();
}
public void showRooms(PhpJSON php)
{
try
{
string roomSap = php.localDatabase.zaznamyNamespace.Find(x => x.New == php.cmbBoxRoom.Text).original_room;
php.txtRoom.Text = roomSap.ToString();
SelectRoom = php.localDatabase.zaznamyRecord.Where(x =>
(x.place_room_sap == roomSap)).ToList();
//TODO:osetritstavnull alebonepsrad
string nullExpresion = php.localDatabase.zaznamyRecord.Where(x => x.place_room_sap.Count() == null).ToString();
foreach (var item in SelectRoom)
{
if (php.localDatabase.zaznamyNamespace.Where(x => x.New) != php.cmbBoxRoom.Text)
{
MessageBox.Show("Daná požiadavka sa nenachádza v tabuľke.");
php.dataGridView1.DataSource = string.Empty;
}
else
{
}
php.dataGridView1.DataSource = SelectRoom;
}
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void shelveSelectMysql(PhpJSON php)
{
string shelveQuery = "SELECT DISTINCT shelve_locker FROM majetok.kteem_record";
php.onDatabase.sqlConnection = new MySqlConnection(php.onDatabase.conString);
php.onDatabase.command = new MySqlCommand(shelveQuery, php.onDatabase.sqlConnection);
php.cmbBoxLocker.Items.Add("");
try
{
php.onDatabase.sqlConnection.Open();
php.onDatabase.myReader = php.onDatabase.command.ExecuteReader();
while (php.onDatabase.myReader.Read())
{
string shelveSelect = php.onDatabase.myReader.GetString("place_locker");
php.cmbBoxLocker.Items.Add(shelveSelect);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void shelveSelect(PhpJSON php)
{
try
{
string shelveQuery = "SELECT DISTINCT place_shelve FROM kteem_record";
using (var conn = new SQLiteConnection(@"Data Source=majetok.db3;Version=3", true))
{
using (cmd = new SQLiteCommand(shelveQuery, conn))
{
conn.Open();
php.cmbBoxShelve.Items.Add("");
reader = cmd.ExecuteReader();
while (reader.Read())
{
//string lockerSelect = reader.GetString("place_shelve");
//php.cmbBoxShelve.Items.Add(lockerSelect);
}
conn.Close();
}
//cmd = new SQLiteCommand(insertCmd, conn);
//cmd.CommandText = insertCmd;
//cmd.ExecuteNonQuery();
}
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
Debug.WriteLine(exc.StackTrace);
MessageBox.Show(exc.Message);
}
}
public void ShowWebsiteUsers(PhpJSON php)
{
sqlConnection = new MySqlConnection(conString);
string query = "SELECT* FROM majetok.kteem_users";
using (MySqlConnection sqlCon = new MySqlConnection(conString))
{
command = new MySqlCommand(query, sqlConnection);
try
{
sqlConnection.Open();
php.txtInput.Text = string.Empty;
php.txtURL.Text = php.URLadresaAllUsers;
RestClient rClient = new RestClient();
rClient.endPoint = php.URLadresaAllUsers;
string strResponse = string.Empty;
strResponse = rClient.MakeRequest();
JsonParser.DeserializeInput(php, strResponse);
}
catch (MySqlException)
{
MessageBox.Show("Žiadne data pre toto ID");
}
}
}
public void roomSelect(PhpJSON php)
{
con = new SQLiteConnection("Data Source=" + dbFile);
try
{
string lockerQuery = "SELECT DISTINCT place_room_sap FROM kteem_record";
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = lockerQuery;
cmd.ExecuteNonQuery();
php.cmbBoxRoom.Items.Add("");
//cmd.Parameters.Add(new SQLiteParameter("@place_room_sap", php.cmbBoxRoom.Text));
//cmd.Parameters.AddWithValue("@place_room_sap", php.cmbBoxRoom.Text.ToString());
reader = cmd.ExecuteReader();
while (reader.Read())
{
//string roomSelect = reader.GetString("place_room_sap");
//php.cmbBoxRoom.Items.Add(roomSelect);
}
con.Close();
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
Debug.WriteLine(exc.StackTrace);
MessageBox.Show(exc.Message);
}
}
public int VlozData(DataGridView dataGridView, PhpJSON php)
{
try
{
using (var conn = new SQLiteConnection("Data Source=" + dbFile + ";Version=3"))
{
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand(conn))
{
for (int i = 0; i < php.dataGridView1.Rows.Count; i++)
{
string StrQuery = @"INSERT INTO kteem_records VALUES ("
+ php.dataGridView1.Rows[i].Cells["id"] + ", "
+ php.dataGridView1.Rows[i].Cells["meta_sap"] + ", "
+ php.dataGridView1.Rows[i].Cells["data_name"].ToString() + ", "
+ php.dataGridView1.Rows[i].Cells["place_room_sap"].ToString() + ", "
+ php.dataGridView1.Rows[i].Cells["place_locker"].ToString() + ", "
+ php.dataGridView1.Rows[i].Cells["place_shelve"] + ");";
comm.CommandText = StrQuery;
comm.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
Debug.WriteLine(exc.StackTrace);
MessageBox.Show(exc.Message);
}
return(0);
}
public void ShowServerRecords(PhpJSON php)
{
MySqlToListRecords(php);
php.txtOutput.Text = string.Empty;
foreach (DataTableRecords du in data_source_records)
{
php.txtOutput.Text += du.ID.ToString() + "\t" + du.meta_sap + "\t" + du.data_name + "\t" + du.place_room_sap + "\t" + du.place_locker + "\t" + du.place_shelve + Environment.NewLine;
}
}
public void ShowServerNamespace(PhpJSON php)
{
MySqlToListNamespace(php);
php.txtOutput.Text = string.Empty;
foreach (DataTableNamespace du in data_source_namespace)
{
php.txtOutput.Text += du.id.ToString() + "\t" + du.original_room + "\t" + du.New + "\t" + Environment.NewLine;
}
}
public void ShowServerUsers(PhpJSON php)
{
MySqlToListUsers(php);
php.txtOutput.Text = string.Empty;
foreach (DataTableUsers du in data_source_users)
{
php.txtOutput.Text += du.id.ToString() + "\t" + du.nickname + "\t" + du.InitializeKey + "\t" + Environment.NewLine;
}
}
public void cmbIndexChanged(PhpJSON php)
{
string shelveAll = "SELECT id, meta_sap, data_name, place_room_sap, place_locker, place_shelve FROM majetok.kteem_record WHERE place_shelve='" + php.cmbBoxShelve.Text + "' AND place_room_sap= '" + php.cmbBoxRoom.Text + "' AND place_locker='" + php.cmbBoxLocker.Text + "'";
php.onDatabase.adapterRoomDb = new MySqlDataAdapter(shelveAll, php.onDatabase.sqlConnection);
php.onDatabase.adapterRoomDb.Fill(php.onDatabase.ds, "kteem_record");
php.dataGridView1.DataSource = php.onDatabase.ds;
php.dataGridView1.DataMember = "kteem_record";
}
public void SelectShelveToCombo(PhpJSON php)
{
try
{
php.cmbBoxShelve.Items.AddRange(php.localDatabase.zaznamyRecord.Select(x => x.place_shelve).Distinct().ToArray());
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void cmbBoxEnabled(PhpJSON php)
{
if (php.cmbBoxRoom.Text == null)
{
php.cmbBoxLocker.Enabled = false;
php.cmbBoxShelve.Enabled = false;
}
else
{
php.cmbBoxLocker.Enabled = true;
php.cmbBoxShelve.Enabled = true;
}
}
public static void DeserializeInput(PhpJSON p, string strDeserializeText)
{
try
{
System.Diagnostics.Debug.Write(strDeserializeText + Environment.NewLine);
p.txtInput.Text = p.txtInput.Text + strDeserializeText + Environment.NewLine;
p.txtInput.SelectionStart = p.txtInput.TextLength;
p.txtInput.ScrollToCaret();
}
catch (Exception ex)
{
System.Diagnostics.Debug.Write(ex.Message.ToString() + Environment.NewLine);
}
}
public void showShelves(PhpJSON php)
{
try
{
SelectShelve = SelectLocker.Where(x =>
(x.place_shelve == php.cmbBoxShelve.Text)).ToList();
foreach (var item in SelectShelve)
{
php.dataGridView1.DataSource = SelectShelve;
}
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void showLockers(PhpJSON php)
{
try
{
SelectLocker = SelectRoom.Where(x =>
(x.place_locker == php.cmbBoxLocker.Text)).ToList();
foreach (var item in SelectLocker)
{
php.dataGridView1.DataSource = SelectLocker;
}
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void MySqlToListRecords(PhpJSON php)
{
string result;
string server = "http://127.0.0.1/test/recordsTable.php";
using (WebClient client = new WebClient())
{
result = client.DownloadString(new Uri(server));
};
data_source_records = null;
data_source_records = JsonConvert.DeserializeObject <List <DataTableRecords> >(result);
//php.dataGridView1.DataSource = data_source;
php.localDatabase.ListRecordToSqlite(data_source_records);
//php.dbz.VlozData(php.dataGridView1, php);
//dbz.ViewSqliteToDb(this);
}
public void RemoveChars(PhpJSON php)
{
try
{
string str = "1000-000090243328-0000";
string strom = str.Substring(9, str.Length - 9);
string stram = str.Substring(9, str.Length - 9);
str.Remove(str.Length - 5);
string mystr = "1000-000090243328-0000";
mystr = mystr.Substring(9, mystr.Length - 14);
php.txtShelve.Text = mystr;
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public static void DeserializeJSON(PhpJSON p, string strJSON)
{
try
{
//var jPerson = JsonConvert.DeserializeObject<dynamic>(strJSON);
var jPerson = JsonConvert.DeserializeObject <JsonParser>(strJSON);
//DeserializeOutput("JSON Object:" + jPerson.ToString());
DeserializeOutput(p, "ID:" + jPerson.ID);
DeserializeOutput(p, "meta_sap:" + jPerson.meta_sap);
DeserializeOutput(p, "data_name:" + jPerson.data_name);
DeserializeOutput(p, "place_room_sap:" + jPerson.place_room_sap);
DeserializeOutput(p, "place_locker:" + jPerson.place_locker);
DeserializeOutput(p, "place_shelve:" + jPerson.place_shelve);
}
catch (Exception ex)
{
DeserializeOutput(p, "Error:" + ex.Message.ToString());
}
}
public void SqliteNamespaceToList(PhpJSON php)
{
zaznamyNamespace = new List <Namespace>();
string selectSqliteTable = "SELECT id, original_room, original_locker, original_shelve, new FROM kteem_namespace";
try
{
con = new SQLiteConnection("Data Source=" + dbFile);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = selectSqliteTable;
reader = cmd.ExecuteReader();
while (reader.Read())
{
zaznamyNamespace.Add(new Namespace()
{
id = reader[0].ToString(),
original_room = reader[1].ToString(),
original_locker = reader[2].ToString(),
original_shelve = reader[3].ToString(),
New = reader[4].ToString()
});
}
reader.Close();
cmd.ExecuteNonQuery();
//List<Record> zaznamySelected = zaznamy.Where(x => ((x.place_room_sap=))).ToList();
foreach (var zaznam in zaznamyNamespace)
{
//php.dataGridView1.DataSource = zaznamyNamespace;
}
con.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void SqliteUserToList(PhpJSON php)
{
zaznamyUser = new List <User>();
string selectSqliteTable = "SELECT id, nickname, InitializeKey FROM kteem_users";
try
{
con = new SQLiteConnection("Data Source=" + dbFile);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = selectSqliteTable;
reader = cmd.ExecuteReader();
while (reader.Read())
{
zaznamyUser.Add(new User()
{
id = reader[0].ToString(),
nickname = reader[1].ToString(),
InitializeKey = reader[2].ToString()
});
}
reader.Close();
cmd.ExecuteNonQuery();
//List<Record> zaznamySelected = zaznamy.Where(x => ((x.place_room_sap=))).ToList();
foreach (var zaznam in zaznamyUser)
{
//php.dataGridView1.DataSource = zaznamyUser;
}
con.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void SelectRoomToCombo(PhpJSON php)
{
try
{
Dictionary <string, string> miestnostiDic = new Dictionary <string, string>();
foreach (var item in php.localDatabase.zaznamyNamespace)
{
miestnostiDic.Add(item.New, item.original_room);
//php.cmbBoxRoom.Items.Add(item.New);
}
List <string> miestnosti = php.localDatabase.zaznamyNamespace.Select(x => x.New).ToList();
miestnosti.Sort();
php.cmbBoxRoom.Items.AddRange(miestnosti.ToArray());
List <string> skrine = php.localDatabase.zaznamyRecord.Select(x => x.place_locker).Distinct().ToList();
skrine.Sort();
php.cmbBoxLocker.Items.AddRange(skrine.ToArray());
List <string> policky = php.localDatabase.zaznamyRecord.Select(x => x.place_shelve).Distinct().ToList();
policky.Sort();
php.cmbBoxShelve.Items.AddRange(policky.ToArray());
//php.cmbBoxLocker.Items.AddRange(php.localDatabase.zaznamyRecord.Select(x => x.place_locker).Distinct().ToArray());
//php.cmbBoxShelve.Items.AddRange(php.localDatabase.zaznamyRecord.Select(x => x.place_shelve).Distinct().ToArray());
//List<string> zaznamySelected = php.localDatabase.zaznamyRecord.Where(x =>
// (x.place_room_sap = php.cmbBoxRoom.Text) &&
// (x.place_locker = php.cmbBoxLocker.Text) &&
// (x.place_shelve = php.cmbBoxShelve.Text)).ToList();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void SqliteRecordToList(PhpJSON php)
{
zaznamyRecord = new List <Record>();
string selectSqliteTable = "SELECT id, meta_prev, meta_book, meta_page, meta_kategoria, meta_kategoria_number, meta_sap, " +
"main_kategoria, main_number, main_year_tuke, main_year_kteem, main_year_record, private, id_owner, id_owner_temp, " +
"data_name, data_sort1, data_sort2, data_production_code, data_price_sk, data_price_eu, data_repair, data_discard, data_rfid, " +
"place_room_sap, place_room, place_locker, place_shelve, borrowed, borrowed_date, dummy1, dummy2, dummy3, dummy4 FROM kteem_record";
try
{
con = new SQLiteConnection("Data Source=" + dbFile);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = selectSqliteTable;
reader = cmd.ExecuteReader();
while (reader.Read())
{
zaznamyRecord.Add(new Record()
{
id = reader[0].ToString(),
meta_prev = reader[1].ToString(),
meta_book = reader[2].ToString(),
meta_page = reader[3].ToString(),
meta_kategoria = reader[4].ToString(),
meta_kategoria_number = reader[5].ToString(),
meta_sap = reader[6].ToString(),
main_kategoria = reader[7].ToString(),
main_number = reader[8].ToString(),
main_year_tuke = reader[9].ToString(),
main_year_kteem = reader[10].ToString(),
main_year_record = reader[11].ToString(),
Private = reader[12].ToString(),
id_owner = reader[13].ToString(),
id_owner_temp = reader[14].ToString(),
data_name = reader[15].ToString(),
data_sort1 = reader[16].ToString(),
data_sort2 = reader[17].ToString(),
data_production_code = reader[18].ToString(),
data_price_sk = reader[19].ToString(),
data_price_eu = reader[20].ToString(),
data_repair = reader[21].ToString(),
data_discard = reader[22].ToString(),
data_rfid = reader[23].ToString(),
place_room_sap = reader[24].ToString(),
place_room = reader[25].ToString(),
place_locker = reader[26].ToString(),
place_shelve = reader[27].ToString(),
borrowed = reader[28].ToString(),
borrowed_date = reader[29].ToString(),
dummy1 = reader[30].ToString(),
dummy2 = reader[31].ToString(),
dummy3 = reader[32].ToString(),
dummy4 = reader[33].ToString()
});
}
reader.Close();
cmd.ExecuteNonQuery();
//List<Record> zaznamySelected = zaznamy.Where(x => ((x.place_room_sap=))).ToList();
foreach (var zaznam in zaznamyRecord)
{
//php.dataGridView1.DataSource = zaznamyRecord;
php.listBoxShow.DataSource = zaznamyRecord;
}
con.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void comboBoxShowData(PhpJSON php)
{
List <Record> shelveAllZoznam = new List <Record>();
string shelveAll = "SELECT id, meta_prev, meta_book, meta_page, meta_kategoria, meta_kategoria_number, meta_sap, " +
"main_kategoria, main_number, main_year_tuke, main_year_kteem, main_year_record, private, id_owner, id_owner_temp, " +
"data_name, data_sort1, data_sort2, data_production_code, data_price_sk, data_price_eu, data_repair, data_discard, data_rfid, " +
"place_room_sap, place_room, place_locker, place_shelve, borrowed, borrowed_date, dummy1, dummy2, dummy3, dummy4 FROM kteem_record " +
"WHERE place_shelve='" + php.cmbBoxShelve.Text +
"' AND place_room_sap= '" + php.cmbBoxRoom.Text +
"' AND place_locker='" + php.cmbBoxLocker.Text + "'";
try
{
php.localDatabase.con = new SQLiteConnection("Data Source=" + php.localDatabase.dbFile);
php.localDatabase.con.Open();
php.localDatabase.cmd = php.localDatabase.con.CreateCommand();
php.localDatabase.cmd.CommandText = shelveAll;
php.localDatabase.reader = php.localDatabase.cmd.ExecuteReader();
while (php.localDatabase.reader.Read())
{
shelveAllZoznam.Add(new Record()
{
id = php.localDatabase.reader[0].ToString(),
meta_prev = php.localDatabase.reader[1].ToString(),
meta_book = php.localDatabase.reader[2].ToString(),
meta_page = php.localDatabase.reader[3].ToString(),
meta_kategoria = php.localDatabase.reader[4].ToString(),
meta_kategoria_number = php.localDatabase.reader[5].ToString(),
meta_sap = php.localDatabase.reader[6].ToString(),
main_kategoria = php.localDatabase.reader[7].ToString(),
main_number = php.localDatabase.reader[8].ToString(),
main_year_tuke = php.localDatabase.reader[9].ToString(),
main_year_kteem = php.localDatabase.reader[10].ToString(),
main_year_record = php.localDatabase.reader[11].ToString(),
Private = php.localDatabase.reader[12].ToString(),
id_owner = php.localDatabase.reader[13].ToString(),
id_owner_temp = php.localDatabase.reader[14].ToString(),
data_name = php.localDatabase.reader[15].ToString(),
data_sort1 = php.localDatabase.reader[16].ToString(),
data_sort2 = php.localDatabase.reader[17].ToString(),
data_production_code = php.localDatabase.reader[18].ToString(),
data_price_sk = php.localDatabase.reader[19].ToString(),
data_price_eu = php.localDatabase.reader[20].ToString(),
data_repair = php.localDatabase.reader[21].ToString(),
data_discard = php.localDatabase.reader[22].ToString(),
data_rfid = php.localDatabase.reader[23].ToString(),
place_room_sap = php.localDatabase.reader[24].ToString(),
place_room = php.localDatabase.reader[25].ToString(),
place_locker = php.localDatabase.reader[26].ToString(),
place_shelve = php.localDatabase.reader[27].ToString(),
borrowed = php.localDatabase.reader[28].ToString(),
borrowed_date = php.localDatabase.reader[29].ToString(),
dummy1 = php.localDatabase.reader[30].ToString(),
dummy2 = php.localDatabase.reader[31].ToString(),
dummy3 = php.localDatabase.reader[32].ToString(),
dummy4 = php.localDatabase.reader[33].ToString()
});
}
php.localDatabase.reader.Close();
php.localDatabase.cmd.ExecuteNonQuery();
php.localDatabase.con.Close();
List <Record> zaznamySelected = shelveAllZoznam.Where(x => ((x.place_room_sap == "L") && (x.place_locker == "1") && (x.place_locker == "1"))).ToList();
foreach (var zaznam in shelveAllZoznam)
{
php.dataGridView1.DataSource = shelveAllZoznam;
}
php.localDatabase.con.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
public void SelectDataSqliteCombobox(PhpJSON php)
{
SelectDistinctRoom(php);
SelectDistinctLocker(php);
SelectDistinctShelve(php);
}
public void SelectDataMysql(PhpJSON php)
{
php.selecting.roomSelectMysql(php);
php.selecting.lockerSelectMysql(php);
php.selecting.shelveSelectMysql(php);
}
