public List <Log> getLogsDates(DateTime from, DateTime to)
{
List <Log> logs = new List <Log>();
string initDate = ToUnixTime(from).ToString();
string endDate = ToUnixTime(to).ToString();
string query = Query.GetLogs(initDate, endDate);
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
logs.Add(new Log()
{
User = reader["username"].ToString(),
Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(),
Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(),
Text = reader["log"].ToString()
});
}
reader.Close();
connection.Close();
return(logs);
}
public List <ClrTypeStats> LoadTypeStat()
{
var list = new List <ClrTypeStats>();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cxion;
cmd.CommandText = "SELECT Id, Name, MethodTable, Count, TotalSize FROM Types";
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = dr.GetInt32(0);
var name = dr.GetString(1);
ulong methodTable = (ulong)dr.GetInt64(2);
var count = dr.GetInt64(3);
var totalSize = (ulong)dr.GetInt64(4);
ClrType type = ClrDump.GetType(methodTable);
if (type == null)
{
type = ClrDump.GetType(name);
}
if (type == null)
{
type = new ClrTypeError(name);
}
var clrTypeStats = new ClrTypeStats(id, type, count, totalSize);
list.Add(clrTypeStats);
}
return(list);
}
public void loadMateriaux()
{
listeMateriauxDatagrid.Rows.Clear();
SQLiteConnection conn = lite.getConnector();
String request = @"SELECT *
FROM F_MATERIEL";
String[] rows;
using (SQLiteCommand cmd = new SQLiteCommand(request, conn))
{
using (SQLiteDataReader sd = cmd.ExecuteReader())
{
while (sd.Read())
{
Double[] result = getMaterielStock(sd.GetInt64(0));
rows = new string[5];
rows[0] = sd.GetInt64(0).ToString();
rows[1] = sd.GetString(1);
if (result[0] > 0)
{
rows[2] = result[0].ToString();
}
if (result[1] > 0)
{
rows[3] = result[1].ToString();
}
if (result[2] > 0)
{
rows[4] = result[2].ToString();
}
listeMateriauxDatagrid.Rows.Add(rows);
}
}
}
}
public static Messages GetMessage(ulong serverid, bool isWelcome)
{
if (NoServer(serverid))
{
NewServer(serverid);
}
Messages mess = new Messages();
using var con = new SQLiteConnection(path);
con.Open();
string stm;
if (isWelcome)
{
stm = "SELECT * FROM WelcomeMessages WHERE id=" + serverid;
}
else
{
stm = "SELECT * FROM ByeMessages WHERE id=" + serverid;
}
using var cmd = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
mess.id = Convert.ToUInt64(rdr.GetInt64(0));
mess.ismessagesonline = rdr.GetString(1);
mess.text = rdr.GetString(2);
mess.channelid = Convert.ToUInt64(rdr.GetInt64(3));
}
return(mess);
}
/// <summary>
/// Чтения документов из выбранного контейнера.
/// </summary>
/// <param name="ct_id">ID контейнера</param>
/// <returns>Коллекцию DocumentMap</returns>
public List <DocumentMap> GetDocumentsL(long ct_id = -1)
{
var reslist = new List <DocumentMap>();
try
{
var stmnt = String.Format("SELECT doc_id, ct_id, created_at, name, parent_id FROM mDocuments WHERE ct_id = {0}", ct_id);
m_sqlCmd.CommandText = stmnt;
SQLiteDataReader r = m_sqlCmd.ExecuteReader();
while (r.Read())
{
var dt = DateTime.Now;
var created = r["created_at"].ToString();
if (!String.IsNullOrEmpty(created))
{
dt = DateTime.Parse(created);
}
reslist.Add(new DocumentMap(r.GetInt64(0), r.GetInt64(1), r["name"].ToString(), dt));
}
r.Close();
}
catch (SQLiteException ex)
{
//MessageBox.Show("Error: " + ex.Message);
}
return(reslist);
}
//Učitavanje aktivnosti korisnika sa baze po pripadajucem id-u i datumu
public static List <AktivnostKorisnika> DbUcitajAktivnostiKorisnikaPoDatumu(int id, DateTime datum)
{
konekcija.Open();
string ucitajAktivnostiKorisnika = "SELECT * FROM aktivnostikorisnika WHERE k_id =" + id + " AND datum = " + datum.ToFileTime();
SQLiteCommand createcmd = new SQLiteCommand(ucitajAktivnostiKorisnika, konekcija);
SQLiteDataReader reader = createcmd.ExecuteReader();
List <AktivnostKorisnika> listaAktivnostiKorisnika = new List <AktivnostKorisnika>();
string getTip = @"SELECT tipoviaktivnosti.naziv FROM tipoviaktivnosti, aktivnostikorisnika WHERE tipoviaktivnosti.id = aktivnostikorisnika.ta_id
AND aktivnostikorisnika.id=" + id;
SQLiteCommand getTipCmd = new SQLiteCommand(getTip, konekcija);
var tip = getTipCmd.ExecuteNonQuery().ToString();
while (reader.Read())
{
AktivnostKorisnika ak = new AktivnostKorisnika(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3),
DateTime.FromFileTime(reader.GetInt64(4)), DateTime.FromFileTime(reader.GetInt64(5)),
DateTime.FromFileTime(reader.GetInt64(6)), reader.GetDouble(7));
listaAktivnostiKorisnika.Add(ak);
}
konekcija.Close();
createcmd.Dispose();
return(listaAktivnostiKorisnika);
}
public string GetSchedulerEvents(Users.NewUser user, int room, string uid)
{
try {
db.CreateDataBase(user.userGroupId, db.scheduler);
List <Event> xx = new List <Event>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(user.userGroupId, dataBase))) {
connection.Open();
string sql = string.Format(@"
SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler WHERE room = {0} {1}"
, room, user.adminType == 0 && uid == null ? "" : string.Format(" AND userId = '{0}'", uid == null ? user.userId : uid));
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Event x = new Event();
x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0);
x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1);
x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4);
x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5);
x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6);
xx.Add(x);
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); }
}
public static List <Zapisnik> Zapisnik_Get_All()
{
var lista = new List <Zapisnik>();
SQLiteCommand com = DB_connection.conn.CreateCommand();
com.CommandText = String.Format(@"SELECT * FROM ZAPISNIK");
SQLiteDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Zapisnik z = new Zapisnik();
z.Id = Convert.ToInt32(reader["ID"]);
if (DateTime.FromFileTime(reader.GetInt64(1)).GetType() != typeof(DBNull))
{
z.Datum = DateTime.FromFileTime(reader.GetInt64(1));
}
if (reader["Ekipa_ID"].GetType() != typeof(DBNull))
{
z.Ekipa_id = Convert.ToInt32(reader["Ekipa_ID"]);
}
z.Ekipa_gost = (string)reader["Ekipa_G"];
z.Rez_dom = Convert.ToInt32(reader["Rez_d"]);
z.Rez_gost = Convert.ToInt32(reader["Rez_g"]);
lista.Add(z);
}
com.Dispose();
return(lista);
}
internal static Transaction CreateFromDatabaseEntry(SQLiteDataReader reader)
{
TransactionCurrency currency = (TransactionCurrency)reader.GetInt32(2);
Transaction t = new Transaction(currency);
t.Id = (ulong)reader.GetInt64(0);
t.Status = (TransactionStatus)reader.GetInt32(1);
t.PaymentAmount = reader.GetInt64(3);
t.MinConfirmations = reader.GetInt32(4);
if (!reader.IsDBNull(5))
{
t.Note = reader.GetString(5);
}
if (!reader.IsDBNull(6))
{
t.SuccessUrl = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
t.FailureUrl = reader.GetString(7);
}
return(t);
}
// Loads All Ledger data (this is public)
public void LoadStudentLedger(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd;
sqlite_cmd = new SQLiteCommand("SELECT * FROM Student_tbl", conn); //
SQLiteDataReader read = sqlite_cmd.ExecuteReader();
StudentFlowPanel.SuspendLayout();
StudentFlowPanel.Controls.Clear();
while (read.Read())
{
sc = new StudentItemControl();
sc.StudentLrn = read.GetInt64(0).ToString(); // LRN
sc.StudentName = read.GetString(1) + " " + read.GetString(2) + " " + read.GetString(3); // fullname
sc.StudentIDLabel.Text = read.GetInt64(0).ToString(); // LRN
sc.StudentNameLabel.Text = read.GetString(1) + " " + read.GetString(2) + " " + read.GetString(3); // fullname
sc.StudentSectionLabel.Text = "Section: " + read.GetString(5); // section
sc.StudentLevelLabel.Text = "Level: " + read.GetInt32(6).ToString(); // level
StudentFlowPanel.Controls.Add(sc);
}
StudentFlowPanel.ResumeLayout();
}
// Filters Ledgers by LRN (uses student_id for testing purposes -> change this to LRN later)
private void LoadStudentLedgerByLRN(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd;
sqlite_cmd = new SQLiteCommand("SELECT * FROM Enrolment_tbl WHERE lrn = @lrn", conn);
sqlite_cmd.Parameters.AddWithValue("@lrn", FilterTextBox.Text);
SQLiteDataReader read = sqlite_cmd.ExecuteReader();
StudentFlowPanel.SuspendLayout();
StudentFlowPanel.Controls.Clear();
while (read.Read())
{
sc = new StudentItemControl();
sc.StudentLrn = read.GetInt64(read.GetOrdinal("lrn")).ToString(); // LRN
sc.StudentName = read.GetString(read.GetOrdinal("first_name")) + " " + read.GetString(read.GetOrdinal("middle_name")) + " " + read.GetString(read.GetOrdinal("surname")); // fullname
sc.StudentIDLabel.Text = read.GetInt64(read.GetOrdinal("lrn")).ToString(); // LRN
sc.StudentNameLabel.Text = read.GetString(read.GetOrdinal("first_name")) + " " + read.GetString(read.GetOrdinal("middle_name")) + " " + read.GetString(read.GetOrdinal("surname")); // fullname
sc.StudentSectionLabel.Text = "Section: " + read.GetString(read.GetOrdinal("section")); // section
sc.StudentLevelLabel.Text = "Level: " + read.GetInt32(read.GetOrdinal("level")).ToString(); // level
sc.StudentSection = read.GetString(read.GetOrdinal("section")); // section gettersetter
sc.StudentLevel = read.GetInt32(read.GetOrdinal("level")).ToString(); // level gettersetter
StudentFlowPanel.Controls.Add(sc);
}
StudentFlowPanel.ResumeLayout();
}
static public Node Find(UInt64 id)
{
string nodesSql = "SELECT * from Nodes WHERE ID = @id;";
try
{
SQLiteCommand command = new SQLiteCommand(nodesSql, Server.Database.Connection);
command.Parameters.AddWithValue("@id", id);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(new Node(
(UInt64)reader.GetInt64(reader.GetOrdinal("ID")),
new Address(reader.GetString(reader.GetOrdinal("Address"))),
(Common.NodeType)reader.GetInt64(reader.GetOrdinal("Type")),
reader.GetBoolean(reader.GetOrdinal("Terminated"))));
}
}
catch (Exception)
{
throw;
}
return(null);
}
public List <Sale> getIncomes(DateTime initDate, DateTime endDate)
{
List <Sale> incomes = new List <Sale>();
string from = ToUnixTime(initDate).ToString();
string to = ToUnixTime(endDate).ToString();
string query = Query.GetIncomes(from, to);
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
incomes.Add(new Sale()
{
Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(),
Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(),
User = reader.GetString(2),
Product = reader.GetString(3),
Quantity = reader.GetDouble(4),
Price = reader.GetDouble(5)
});
}
reader.Close();
connection.Close();
return(incomes);
}
public List <Sale> getAllSales()
{
List <Sale> sales = new List <Sale>();
string query = Query.GET_ALL_SALES;
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sales.Add(new Sale()
{
Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(),
Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(),
User = reader.GetString(2),
Product = reader.GetString(3),
Quantity = reader.GetDouble(4),
Price = reader.GetDouble(5)
});
}
reader.Close();
connection.Close();
return(sales);
}
public string Get(string userId, string id)
{
Event x = new Event();
try {
//db.CreateDataBase(userId, db.scheduler);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler WHERE rowid = '{0}'", id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0);
x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1);
x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4);
x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5);
x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6);
}
}
}
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, id, userId, "Scheduler", "Get");
return(JsonConvert.SerializeObject(x, Formatting.None));
}
}
private SimpleItem[] SimpleItemList(string table)
{
List <SimpleItem> itemList = new List <SimpleItem>();
string queryString = "select * from " + table + " order by position";
// Get status list from database.
using (SQLiteConnection connection = new SQLiteConnection(strConnection))
using (SQLiteCommand command = new SQLiteCommand(queryString, connection))
{
connection.Open();
SQLiteDataReader reader = command.ExecuteReader();
int idColumn = reader.GetOrdinal("id");
int positionColumn = reader.GetOrdinal("position");
int titleColumn = reader.GetOrdinal(table);
int noteColumn = reader.GetOrdinal("note");
while (reader.Read())
{
long id = reader.GetInt64(idColumn);
long position = reader.GetInt64(positionColumn);
string title = reader.GetString(titleColumn);
string note = reader.GetString(noteColumn);
itemList.Add(new SimpleItem(id, position, title, note));
}
}
return(itemList.ToArray());
}
static public Account Find(string username)
{
string accountsSql = "SELECT * from Accounts WHERE Username = @username;";
try
{
SQLiteCommand command = new SQLiteCommand(accountsSql, Server.Database.Connection);
command.Parameters.AddWithValue("@username", username);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(new Account(
reader.GetString(reader.GetOrdinal("Username")),
reader.GetString(reader.GetOrdinal("Email")),
reader.GetString(reader.GetOrdinal("Password")),
(UInt64)reader.GetInt64(reader.GetOrdinal("NodeID")),
reader.GetInt32(reader.GetOrdinal("Reputation")),
reader.GetInt64(reader.GetOrdinal("Credits")),
reader.GetBoolean(reader.GetOrdinal("Banned"))));
}
}
catch (Exception)
{
throw;
}
return(null);
}
protected override Task ConvertReaderToObject(SQLiteDataReader reader)
{
//log.Debug("in reader with task: "+reader.GetString(0)+" "+ reader.GetInt64(1) + " " + reader.GetString(2) + " " + reader.GetString(3) + " " + reader.GetValue(4));
Task result = new Task(reader.GetInt64(0), reader.GetInt64(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.IsDBNull(5) ? null : reader.GetString(5), reader.GetInt64(6), reader.GetInt64(7));
return(result);
}
// Statistika za razdoblje po potrosnji
public static List <AktivnostKorisnika> StatistikaRazdobljaPoPotrosnji(DateTime datumOd, DateTime datumDo, int id)
{
datumDo.AddHours(23);
datumDo.AddMinutes(59);
datumDo.AddSeconds(59);
konekcija.Open();
string statistikaRazdoblja = @"SELECT * FROM aktivnostikorisnika WHERE k_id=" + id + " AND datum>=" +
datumOd.ToFileTime() + " AND datum<=" + datumDo.ToFileTime() + " ORDER BY potrosnja";
SQLiteCommand cmd = new SQLiteCommand(statistikaRazdoblja, konekcija);
SQLiteDataReader reader = cmd.ExecuteReader();
List <AktivnostKorisnika> listaAktivnostiKorisnika = new List <AktivnostKorisnika>();
while (reader.Read())
{
AktivnostKorisnika ak = new AktivnostKorisnika(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3),
DateTime.FromFileTime(reader.GetInt64(4)), DateTime.FromFileTime(reader.GetInt64(5)),
DateTime.FromFileTime(reader.GetInt64(6)), reader.GetDouble(7));
listaAktivnostiKorisnika.Add(ak);
}
konekcija.Close();
cmd.Dispose();
return(listaAktivnostiKorisnika);
}
private static void ReadPlayerInfo(SQLiteDataReader reader, Player player)
{
player.Id = (uint)reader.GetInt32(0);
player.Name = reader.GetString(1);
player.Gender = (Gender)reader.GetByte(2);
player.Vocation = (Vocation)reader.GetByte(3);
player.Level = (ushort)reader.GetInt16(4);
player.MagicLevel = reader.GetByte(5);
player.Experience = (uint)reader.GetInt32(6);
player.MaxHealth = (ushort)reader.GetInt16(7);
player.MaxMana = (ushort)reader.GetInt16(8);
player.Capacity = (uint)reader.GetInt32(9);
player.Outfit.LookType = (ushort)reader.GetInt16(10);
player.Outfit.Head = reader.GetByte(11);
player.Outfit.Body = reader.GetByte(12);
player.Outfit.Legs = reader.GetByte(13);
player.Outfit.Feet = reader.GetByte(14);
player.Outfit.Addons = reader.GetByte(15);
if (reader.GetInt64(20) > 0)
{
int x = reader.GetInt32(16);
int y = reader.GetInt32(17);
int z = reader.GetInt32(18);
player.SavedLocation = new Location(x, y, z);
player.Direction = (Direction)reader.GetByte(19);
player.LastLogin = new DateTime(reader.GetInt64(20));
}
player.Speed = (ushort)(220 + (2 * (player.Level - 1)));
}
public static List <Tag> getTagList(long plc_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Tag WHERE plc_id =" + plc_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Tag(reader.GetInt64(0), reader.GetString(1),
reader.GetInt32(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetInt64(6)
));
}
}
return(list);
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createTagTable();
}
}
return(new List <Tag>());
}
public string getLatestVersionFormDB(string startTime, string endTime)
{
int recordSerial = 0;
string result = "{\"versions\" : [";;
using (SQLiteConnection conn = new SQLiteConnection("Data source = versions.db"))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = string.Format(
"SELECT startTime, endTime, version FROM TimeNameMap where startTime >= {0} and startTime <= {1} or endTime >= {0} and endTime <= {1} order by startTime",
startTime,
endTime);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long start = reader.GetInt64(0);
long end = reader.GetInt64(1);
long version = reader.GetInt64(2);
if (recordSerial > 0)
{
result += ",";
}
result += "{\"Version\" :" + version;
result += ", \"start\" : " + start;
result += ", \"end\" : " + end + "}";
recordSerial++;
}
result += "], \"length\" :" + recordSerial + "}";
}
}
return(result);
}
private DatabaseStarSystem ReadStarSystemEntry(SQLiteCommand cmd)
{
string systemName = string.Empty;
long? systemAddress = null;
long? edsmId = null;
string starSystemJson = string.Empty;
string comment = string.Empty;
DateTime lastUpdated = DateTime.MinValue;
DateTime?lastVisit = null;
int totalVisits = 0;
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "systemaddress")
{
systemAddress = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i);
}
if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "edsmid")
{
edsmId = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i);
}
if (rdr.GetName(i) == "name")
{
systemName = rdr.IsDBNull(i) ? null : rdr.GetString(i);
}
if (rdr.GetName(i) == "starsystem")
{
starSystemJson = rdr.IsDBNull(i) ? null : rdr.GetString(i);
}
if (rdr.GetName(i) == "comment")
{
comment = rdr.IsDBNull(i) ? null : rdr.GetString(i);
}
if (rdr.GetName(i) == "starsystemlastupdated")
{
lastUpdated = rdr.IsDBNull(i) ? DateTime.MinValue : rdr.GetDateTime(i).ToUniversalTime();
}
if (rdr.GetName(i) == "lastvisit")
{
lastVisit = rdr.IsDBNull(i) ? null : (DateTime?)rdr.GetDateTime(i).ToUniversalTime();
}
if (rdr.GetName(i) == "totalvisits")
{
totalVisits = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i);
}
}
}
}
return(new DatabaseStarSystem(systemName, systemAddress, edsmId, starSystemJson)
{
comment = comment,
lastUpdated = lastUpdated,
lastVisit = lastVisit,
totalVisits = totalVisits
});
}
public static int InsertResultItem(Simulation.GameRecord record, SQLiteCommand command)
{
string tableStr = $"Tbl{record.holeUniquePrime}";
command.CommandText = $"SELECT * FROM {tableStr} where Flop = {record.flopUniquePrime}";
SQLiteDataReader dr = command.ExecuteReader();
if (record.winFlag == 1)
{
// read integer in Wins column
dr.Read();
long updatedWinCount = dr.GetInt64(1) + 1;
// update integer in Wins column
dr.Close();
command.CommandText = $"UPDATE {tableStr} SET W = {updatedWinCount} WHERE Flop = {record.flopUniquePrime};";
}
else if (record.winFlag == 0)
{
// read integer in Losses column
dr.Read();
long updatedLossCount = dr.GetInt64(2) + 1;
// update integer in Losses column
dr.Close();
command.CommandText = $"UPDATE {tableStr} SET L = {updatedLossCount} WHERE Flop = {record.flopUniquePrime};";
}
return(command.ExecuteNonQuery());
}
public string Load(string userGroupId, string userId)
{
db.CreateDataBase(userGroupId, db.scheduler);
List <Event> xx = new List <Event>();
try {
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(dataBase))) {
connection.Open();
string sql = "SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler";
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
xx = new List <Event>();
while (reader.Read())
{
Event x = new Event();
x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0);
x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1);
x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4);
x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5);
x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6);
xx.Add(x);
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) { return(e.Message); }
}
internal static float CalculatePreFlopPercentage(long holeId, SQLiteCommand cmd)
{
/**********************************************
* Each table corresponds to a specific set of hole
* cards.Add up all the wins and losses of in a given
* table and take the ratio of wins to the total games
* that the holecards have participated in (wins + losses)
* to get the pre-flop probability of winning.
***********************************************/
Trace.WriteLine($"{nameof(CalculatePreFlopPercentage)} method running with HoleId = {holeId}");
long _winTotal = 0;
long _lossTotal = 0;
// need to test this command in Sqlite DB Viewer
cmd.CommandText = $"SELECT W, L FROM Tbl{holeId};";
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
Trace.Assert(dr.HasRows == true);
while (dr.Read())
{
// note the indices start at 0 because we only
// selected the win and loss columns
_winTotal += dr.GetInt64(0);
_lossTotal += dr.GetInt64(1);
}
}
// return odds of winning as a percentage of 100
return((float)(100.0 * (float)_winTotal / (float)(_winTotal + _lossTotal)));
}
public void Execute()
{
var unicodeEntryList = new List <UnicodeEntry>();
try
{
SQLiteConnection connection = new SQLiteConnection("Data Source=unicode.sqlite;");
connection.Open();
var command = new SQLiteCommand("SELECT * FROM unicode;", connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string resultString = reader.GetString(1);
unicodeEntryList.Add(new UnicodeEntry(
reader.IsDBNull(0) ? 0 : reader.GetInt64(0),
resultString.Length > 0 ? resultString[0] : ' ',
reader.IsDBNull(2) ? 0 : reader.GetInt64(2),
reader.IsDBNull(3) ? 0 : reader.GetInt64(3)
));
}
}
catch (SQLiteException e)
{
Console.WriteLine(e);
}
}
internal static float CalculatePostFlopPercentage(long holeId, long flopId, SQLiteCommand cmd)
{
/**********************************************
* Each table corresponds to a specific set of hole
* cards. Each FlopId corresponds to a specific
* set of flop cards. Take the ratio of wins
* to the total games (wins + losses) that hole & flop combination
* has participated in to get the post-flop
* probability of winning.
***********************************************/
long _winTotal = 0;
long _lossTotal = 0;
// need to test this command in Sqlite DB Viewer
cmd.CommandText = $"SELECT W, L FROM Tbl{holeId} WHERE Flop = {flopId};";
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// note the indices start at 0 because we only
// selected the win and loss columns
_winTotal += dr.GetInt64(0);
_lossTotal += dr.GetInt64(1);
}
}
// return odds of winning as a percentage of 100
return((float)(100.0 * (float)_winTotal / (float)(_winTotal + _lossTotal)));
}
public IList <DotNetMetric> GetByPeriod(DateTimeOffset fromTime, DateTimeOffset toTime)
{
using var connection = new SQLiteConnection(_connector.GetStringConnection());
using var cmd = new SQLiteCommand(connection);
cmd.Connection.Open();
// прописываем в команду SQL запрос на получение всех данных из таблицы
cmd.CommandText = $"SELECT id, value, time FROM {Table} WHERE time BETWEEN @fromTime AND @toTime;";
cmd.Parameters.AddWithValue("@fromTime", fromTime.ToUnixTimeSeconds());
cmd.Parameters.AddWithValue("@toTime", toTime.ToUnixTimeSeconds());
cmd.Prepare();
var returnList = new List <DotNetMetric>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
// пока есть что читать -- читаем
while (reader.Read())
{
// добавляем объект в список возврата
returnList.Add(new DotNetMetric
{
Id = reader.GetInt64(0),
Value = reader.GetInt32(1),
// налету преобразуем прочитанные секунды в метку времени
Time = DateTimeOffset.FromUnixTimeSeconds(reader.GetInt64(2))
});
}
}
return(returnList);
}
public static List <Dogadaj> DohavtiSve()
{
BP.otvoriKonekciju();
List <Dogadaj> listaDogadaja = new List <Dogadaj>();
SQLiteCommand command = BP.konekcija.CreateCommand();
//Umetanje podataka u tablicu
command.CommandText = "Select * from dogadaj";
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dogadaj temp = new Dogadaj();
//temp.Datum = DateTime.FromFileTime(reader.GetInt64(2));
temp.Id = (int)(Int64)reader["id"];
temp.Datum = DateTime.FromFileTime(reader.GetInt64(1));
temp.Opis = (string)reader["opis"];
temp.Datum = DateTime.FromFileTime(reader.GetInt64(1));
temp.Mjesto = (string)reader["mjesto"];
listaDogadaja.Add(temp);
}
reader.Dispose();
command.Dispose();
BP.zatvoriKonekciju();
return(listaDogadaja);
}
