public IEnumerable <DBUserTradeOrder> ReadOrders(ICurrencyIdentity aCurrency = null, int?aExchangeID = null)
{
if (!Initialized)
{
throw new Exception("Save manager not initialized");
}
string lQry = "SELECT A.InternalID, A.ID, A.Market, A.Quantity, A.OpenTime, A.Rate, A.Completed, A.Cancelled, A.Status, A.CoinTxID, A.CurrencyTicker, A.CurrencyId, A.Name, A.Stop, A.ProfileID, B.ExchangeID FROM ExchangeTx A " +
"inner join Relationship_ExchangeID_OrderID B ON A.InternalID = B.InternalOrderID WHERE 1";
if (aCurrency != null)
{
lQry += $" AND ( A.CurrencyId = {aCurrency.Id} OR A.CurrencyTicker = '{aCurrency.Ticker}')";
}
if (aExchangeID.HasValue)
{
lQry += $" AND B.ExchangeID = {aExchangeID}";
}
var lListOrders = new List <DBUserTradeOrder>();
using (SQLiteConnection lConnection = new SQLiteConnection(FConnectionString))
using (SQLiteCommand command = new SQLiteCommand(lQry, lConnection))
{
try
{
lConnection.Open();
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
decimal lRate = rdr.GetDecimal(5);
decimal lStop = rdr.GetDecimal(13);
DBUserTradeOrder lOrder = new DBUserTradeOrder
{
InternalID = rdr.GetInt32(0),
ID = rdr[1] as string,
MarketID = rdr.GetString(2),
SentQuantity = rdr.GetDecimal(3),
OpenTime = rdr.GetDateTime(4),
Rate = lRate,
Completed = rdr.GetBoolean(6),
Cancelled = rdr.GetBoolean(7),
Status = (OrderStatus)rdr.GetInt32(8),
CoinTxID = rdr.GetString(9),
CurrencyTicker = rdr.GetString(10),
CurrencyId = rdr.IsDBNull(11) ? 0 : rdr.GetInt64(11),
Name = rdr.GetString(12),
StopPrice = lStop == 0 ? lRate : lStop,
ProfileID = rdr.GetInt32(14),
ExchangeID = rdr.GetInt32(15),
};
lListOrders.Add(lOrder);
}
}
return(lListOrders);
}
catch (Exception ex)
{
Universal.Log.Write(Universal.LogLevel.Error, $"Error reading enchange orders from db. Exception {ex}");
throw;
}
finally
{
lConnection.Close();
}
}
}
public static void GetFullGradesList(ref LinkedList LL)
{
string SQLCommandString = "SELECT * FROM Grades_Table";
SQLiteConnection SQLConnection = Database.CreateConnection();
SQLiteCommand SQLCMD = SQLConnection.CreateCommand();
SQLCMD.CommandText = SQLCommandString;
SQLiteDataReader SQLReader = SQLCMD.ExecuteReader();
if (SQLReader.HasRows)
{
while (SQLReader.Read())
{
LL.AddLast(SQLReader.GetInt32(0), SQLReader.GetString(1), SQLReader.GetString(2), SQLReader.GetString(3), SQLReader.GetInt32(4), SQLReader.GetString(5), SQLReader.GetString(6), SQLReader.GetDecimal(7));
}
}
SQLConnection.Close();
}
/// <summary>
/// Wyszukuje i pobiera z bazy danych listę wszystkich
/// faktur wraz ze szczegółami dotyczącymi każdej z faktur.
/// </summary>
/// <returns>lista faktur wyszukana w bazie danych</returns>
internal IList <Faktura> pobierzListeFaktur()
{
IList <Faktura> listaFaktur = new List <Faktura>();
try
{
SQLiteCommand cmd;
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText =
"select f.ID_Faktury, f.Data_Wystawienia, f.ID_Klienta, f.Nr_faktury " +
" from Faktury as f " +
" order by f.Data_Wystawienia";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader != null && reader.Read())
{
Faktura fak = new Faktura();
fak.Id = reader.GetInt32(0);;
fak.Data = reader.GetDateTime(1);
fak.ID_Klienta = reader.GetInt32(2);
fak.Nr = reader.GetInt32(3);
listaFaktur.Add(fak);
}
reader.Close();
connection.Close();
connection.Open();
cmd = connection.CreateCommand();
cmd.CommandText =
"select sk.ID_Faktury, sk.ID_Sprzedazy, sk.Liczba, sk.ID_Ksiazki, sk.Cena " +
" from Sprzedaz_Ksiazek as sk " +
" order by sk.ID_Faktury";
reader = cmd.ExecuteReader();
List <int> Id_sprzedanychKsiazek = new List <int>();
listaFaktur[0].listaSprzedanychKsiazek = new List <SprzedazKsiazek>();
int licznik = 0;
while (reader != null && reader.Read())
{
int id = reader.GetInt32(0);
for (licznik = 0; licznik < listaFaktur.Count; licznik++)
{
if (listaFaktur[licznik].Id == id)
{
break;
}
}
SprzedazKsiazek sprz_ksi = new SprzedazKsiazek();
sprz_ksi.Id = reader.GetInt32(1);
sprz_ksi.Ilosc = reader.GetInt32(2);
sprz_ksi.IdKsiazki = reader.GetInt32(3);
sprz_ksi.Cena = reader.GetDecimal(4);
listaFaktur[licznik].listaSprzedanychKsiazek.Add(sprz_ksi);
}
reader.Close();
connection.Close();
IList <Ksiazki> listaKsiazek = pobierzListeKsiazek();
for (int i = 0; i < listaFaktur.Count; i++)
{
for (int j = 0; j < listaFaktur[i].listaSprzedanychKsiazek.Count; j++)
{
foreach (Ksiazki item in listaKsiazek) //alternatywnie - lepiej tak przypisywać niż pobierać z bazy pojedynczo
{
if (listaFaktur[i].listaSprzedanychKsiazek[j].IdKsiazki == item.ISBN)
{
listaFaktur[i].listaSprzedanychKsiazek[j].ksi = item;
break;
}
}
//listaFaktur[i].listaSprzedanychKsiazek[j].ksi = pobierzKsiazkePoId(listaFaktur[i].listaSprzedanychKsiazek[j].IdKsiazki);
}
listaFaktur[i].klient = pobierzKlientaPoId(listaFaktur[i].ID_Klienta);
}
}
catch (SQLiteException ex)
{
MessageBox.Show("Błąd bazy danych!\n" + ex.Message);
}
catch (Exception ex2)
{
MessageBox.Show(ex2.Message);
}
return(listaFaktur);
}
private Diretorio novoObjeto(SQLiteDataReader dr)
{
Diretorio ret = new Diretorio();
Aba aba = new Aba();
Tipo tipo;
aba.Codigo = dr.GetInt32(0);
ret.Codigo = dr.GetInt32(1);
ret.Ordem = dr.GetInt32(2);
if (!dr.IsDBNull(3))
{
ret.CodDirPai = dr.GetInt32(3);
}
else
{
ret.CodDirPai = -1;
}
if (!dr.IsDBNull(4))
{
ret.Nome = dr.GetString(4);
}
ret.Tamanho = dr.GetDecimal(5);
tipo = new Tipo(dr.GetString(6)[0], "");
ret.Modificado = Rotinas.StringToDate(dr.GetString(7));
if (!dr.IsDBNull(8))
{
ret.Atributos = dr.GetString(8);
}
if (!dr.IsDBNull(9))
{
ret.Caminho = dr.GetString(9);
}
if (!dr.IsDBNull(10))
{
aba.Nome = dr.GetString(10);
}
ret.Aba = aba;
if (!dr.IsDBNull(11))
{
ret.NomePai = dr.GetString(11);
}
if (!dr.IsDBNull(12))
{
ret.CaminhoPai = dr.GetString(12);
}
ret.TamanhoFormatado =
DiretorioBO.Instancia.MontaTamanho(ret.Tamanho);
if (tipo.Codigo == 'D')
{
tipo.Nome = "Diretório";
}
else
{
tipo.Nome = "Arquivo";
}
ret.Tipo = tipo;
ret.ModificadoFormatado = Rotinas.formataDate(
Rotinas.FORMATO_DATAHORA, ret.Modificado);
return(ret);
}
static void Main(string[] args)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
string docPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
List <string> dirs = new List <string>(Directory.EnumerateDirectories(docPath));
// Thread.Sleep(10000);
;
int[,] data =
{
{ 1, 3, 4 },
{ 12, 5, 1 },
{ 1, 4, 7 }
};
try
{
// Console.WriteLine(data[0, 2]);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
// Console.WriteLine("RunTime " + elapsedTime);
// DateTime start = new DateTime();
DateTime currentTime = DateTime.Now;
// Console.WriteLine(currentTime);
DateTime startDate = new DateTime(2000, 1, 1);
DateTime[] newDays = new DateTime[366];
/*
* for (int i = 0; i < newDays.Length; i++)
* {
* newDays[i] = (startDate.AddDays(i));
* Console.WriteLine(newDays[i].ToShortDateString());
* }
*/
List <double> myList = new List <double>();
for (int i = 0; i < 10; i++)
{
myList.Add(i);
}
foreach (var value in myList)
{
// Console.WriteLine(value);
}
Dictionary <string, double> newDict = new Dictionary <string, double>();
newDict.Add("Atlar", 5.0);
newDict.Add("Atkafasi", 123.0);
// Console.WriteLine(newDict["Atkafasi"]);
string[] names = { "at", "KAfasi", "Atlarca" };
// names.ToDictionary()
Dictionary <string, int> nDict = new Dictionary <string, int>()
{
{ "Atlar", 5 },
{ "At", 3 },
{ "at", 41 }
};
// myList.ForEach(p => Console.WriteLine("{0}", p));
var selected = myList.Select(p => p == 5);
// foreach (var b in selected)
// {
// Console.WriteLine(b);
// }
List <Todo> todo = new List <Todo>()
{
new Todo {
Description = "Task 1", Hours = 15, status = Status.Compleated
},
new Todo {
Description = "Task 2", Hours = 12, status = Status.Deleted
},
new Todo {
Description = "Task 3", Hours = 5, status = Status.InProgress
},
new Todo {
Description = "Task 4", Hours = 3, status = Status.NotStarted
},
new Todo {
Description = "Task 5", Hours = 12, status = Status.Compleated
},
new Todo {
Description = "Task 6", Hours = 1, status = Status.OnHold
},
new Todo {
Description = "Task 7", Hours = 9, status = Status.NotStarted
},
};
var newselect = todo.Where(p => p.Hours > 12);
Console.ForegroundColor = ConsoleColor.White;
foreach (var item in todo)
{
// Console.WriteLine(item.Description);
}
foreach (var b in newselect)
{
// Console.WriteLine(b.Description);
}
// todo.ForEach(p => Console.WriteLine("{0},{1:C},{2:F}", p.Description, p.Hours, p.status));
string cs = @"URI=file:E:\MGM\data.db";
using var con = new SQLiteConnection(cs);
con.Open();
int stationNo = 17018;
string stm = string.Format("SELECT * FROM temp t where t.ist_no = {0} LIMIT 10", stationNo);
using var cmd = new SQLiteCommand(stm, con);
try
{
using SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// var stationId = rdr.GetDecimal(0);
Console.WriteLine("{0} {1} {2} {3} {4} {5} {6}", rdr.GetDecimal(0), rdr.GetString(1),
rdr.GetDecimal(2),
rdr.GetDecimal(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetString(6));
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
System.Timers.Timer myTimer = new System.Timers.Timer(1000);
myTimer.Elapsed += MyTimer_Elapsed;
myTimer.Start();
}
public Dictionary <string, object> FindById(string table, string keyField, int keyValue)
{
SQLiteConnection conn = DatabaseManager.GetConnection();
SQLiteDataReader dr = null;
conn.Open();
try
{
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT " + BuildFieldValues() + " FROM " + table + " WHERE " + keyField + " = :" + keyField;
cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add(":" + keyField, keyValue);
SQLiteParameter parameter = new SQLiteParameter();
parameter.DbType = DbType.Int32;
//sqlLiteParameter.DbType = (System.Data.DbType)parameter.Type;
parameter.ParameterName = ":" + keyField;
parameter.Value = keyValue;
cmd.Parameters.Add(parameter);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Dictionary <String, object> vals = new Dictionary <string, object>();
int i = 0;
foreach (string s in _fields.Keys)
{
DbField f = (DbField)_fields[s];
switch (f.Type)
{
case DbType.Int32:
try { vals.Add(s, dr.GetDecimal(i)); }
catch { vals.Add(s, 0); }
break;
case DbType.String:
try { vals.Add(s, dr.GetString(i)); }
catch { vals.Add(s, ""); }
break;
//case DbTypes.Varchar:
// try { vals.Add(s, dr.GetString(i)); }
// catch { vals.Add(s, ""); }
// break;
case DbType.DateTime:
try { vals.Add(s, dr.GetDateTime(i)); }
catch { vals.Add(s, null); }
break;
case DbType.Binary:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, null); }
break;
case DbType.Boolean:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, ""); }
break;
case DbType.Double:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, 0); }
break;
}
i++;
}
return(vals);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dr != null)
{
dr.Dispose();
}
conn.Close();
}
return(null);
}
public decimal ReadDecimal(SQLiteDataReader reader, string columnName)
{
int ordinal = reader.GetOrdinal(columnName);
return(reader.IsDBNull(ordinal) ? 0m : reader.GetDecimal(ordinal));
}
public static Decimal GetDBDecimal(string SqlFieldName, SQLiteDataReader Reader)
{
int columnIndex = Reader.GetOrdinal(SqlFieldName);
return(Reader[SqlFieldName].Equals(DBNull.Value) ? 0 : Reader.GetDecimal(columnIndex));
}
private void AddFromRecordSet(SQLiteDataReader rs)
{
try
{
if (!rs.IsDBNull(0))
{
_backup_lsn = rs.GetDecimal(0);
}
if (!rs.IsDBNull(1))
{
_create_lsn = rs.GetDecimal(1);
}
if (!rs.IsDBNull(2))
{
_data_space_id = rs.GetInt32(2);
}
if (!rs.IsDBNull(3))
{
_database_id = rs.GetInt32(3);
}
if (!rs.IsDBNull(4))
{
_differential_base_guid = rs.GetGuid(4);
}
if (!rs.IsDBNull(5))
{
_differential_base_lsn = rs.GetDecimal(5);
}
if (!rs.IsDBNull(6))
{
_differential_base_time = rs.GetDateTime(6);
}
if (!rs.IsDBNull(7))
{
_drop_lsn = rs.GetDecimal(7);
}
if (!rs.IsDBNull(8))
{
_file_guid = rs.GetGuid(8);
}
if (!rs.IsDBNull(9))
{
_file_id = rs.GetInt32(9);
}
if (!rs.IsDBNull(10))
{
_growth = rs.GetInt32(10);
}
if (!rs.IsDBNull(11))
{
_is_media_read_only = rs.GetBoolean(11);
}
if (!rs.IsDBNull(12))
{
_is_name_reserved = rs.GetBoolean(12);
}
if (!rs.IsDBNull(13))
{
_is_percent_growth = rs.GetBoolean(13);
}
if (!rs.IsDBNull(14))
{
_is_read_only = rs.GetBoolean(14);
}
if (!rs.IsDBNull(15))
{
_is_sparse = rs.GetBoolean(15);
}
if (!rs.IsDBNull(16))
{
_max_size = rs.GetInt32(16);
}
if (!rs.IsDBNull(17))
{
_name = rs.GetString(17);
}
if (!rs.IsDBNull(18))
{
_physical_name = rs.GetString(18);
}
if (!rs.IsDBNull(19))
{
_read_only_lsn = rs.GetDecimal(19);
}
if (!rs.IsDBNull(20))
{
_read_write_lsn = rs.GetDecimal(20);
}
if (!rs.IsDBNull(21))
{
_redo_start_fork_guid = rs.GetGuid(21);
}
if (!rs.IsDBNull(22))
{
_redo_start_lsn = rs.GetDecimal(22);
}
if (!rs.IsDBNull(23))
{
_redo_target_fork_guid = rs.GetGuid(23);
}
if (!rs.IsDBNull(24))
{
_redo_target_lsn = rs.GetDecimal(24);
}
if (!rs.IsDBNull(25))
{
_size = rs.GetInt32(25);
}
if (!rs.IsDBNull(26))
{
_state = (FileState)rs.GetByte(26);
}
if (!rs.IsDBNull(27))
{
_state_desc = rs.GetString(27);
}
if (!rs.IsDBNull(28))
{
_type = (File_Type)rs.GetByte(28);
}
if (!rs.IsDBNull(29))
{
_type_desc = rs.GetString(29);
}
}
catch (Exception)
{
throw;
}
}
public Article GetItem(SQLiteConnection con, string itmref)
{
using (SQLiteCommand cmd = con.CreateCommand())
{
cmd.CommandText = @"SELECT
ITM.ITMREF,
ITM.ITMDES1,
ITM.ITMSTA,
ITM.TSICOD0,
ITM.TSICOD1,
ITM.TSICOD2,
ITM.TSICOD3,
ITM.TSICOD4,
ITM.TCLCOD,
ITM.STU,
ITM.LASCUNDAT,
ITM.PHYSTO,
ITM.MFMQTY,
ITM.FLGMVT,
ITM.FLGCUN,
ITM.FLGMFM,
ITM.FLGSTO,
ITM.CUNFLG,
ITM.CUNLISNUM
FROM ITMMASTER ITM
WHERE ITM.ITMREF=@ITMREF";
cmd.Parameters.AddWithValue("@ITMREF", itmref);
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
Article res = new Article();
res.Itmref = rd.GetString(0);
res.Itmdes = rd.GetString(1);
res.Itmsta = (int)rd.GetByte(2);
res.Tsicod0 = rd.GetString(3);
res.Tsicod1 = rd.GetString(4);
res.Tsicod2 = rd.GetString(5);
res.Tsicod3 = rd.GetString(6);
res.Tsicod4 = rd.GetString(7);
res.Tclcod = rd.GetString(8);
res.Stu = rd.GetString(9);
res.Lascundat = rd.IsDBNull(10) ? DateTime.MinValue : rd.GetDateTime(10);
res.Physto = rd.IsDBNull(11) ? -1 : (double)rd.GetDecimal(11);
res.Mfmqty = rd.IsDBNull(12) ? -1 : (double)rd.GetDecimal(12);
res.Mvt = rd.GetByte(13) != 0;
res.Cun = rd.GetByte(14) != 0;
res.Mfm = rd.GetByte(15) != 0;
res.Sto = rd.GetByte(16) != 0;
res.Cunflg = rd.GetByte(17) != 0;
res.Cunlisnum = rd.IsDBNull(18) ? string.Empty : rd.GetString(18);
return(res);
}
}
}
return(null);
}
private void SetValue(object instance, PropertyInfo propertyInfo, SQLiteDataReader reader)
{
ORMProperty[] props = (ORMProperty[])propertyInfo.GetCustomAttributes(typeof(ORMProperty), false);
if (props.Length > 0)
{
if (propertyInfo.PropertyType == typeof(String))
{
propertyInfo.SetValue(instance, reader.GetString(reader.GetOrdinal(props[0].FieldName)));
}
else if (propertyInfo.PropertyType == typeof(Boolean))
{
propertyInfo.SetValue(instance, (Int64)reader[props[0].FieldName] > 0 ? true : false);
}
else if (propertyInfo.PropertyType == typeof(Int32))
{
propertyInfo.SetValue(instance, reader.GetInt32(reader.GetOrdinal(props[0].FieldName)));
}
else if (propertyInfo.PropertyType == typeof(Int64))
{
propertyInfo.SetValue(instance, reader.GetInt64(reader.GetOrdinal(props[0].FieldName)));
}
else if (propertyInfo.PropertyType == typeof(Decimal))
{
propertyInfo.SetValue(instance, reader.GetDecimal(reader.GetOrdinal(props[0].FieldName)));
}
else if (propertyInfo.PropertyType == typeof(Double))
{
propertyInfo.SetValue(instance, reader.GetDouble(reader.GetOrdinal(props[0].FieldName)));
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
if (reader[props[0].FieldName].GetType() != typeof(System.DBNull))
{
propertyInfo.SetValue(instance, reader.GetDateTime(reader.GetOrdinal(props[0].FieldName)));
}
else
{
propertyInfo.SetValue(instance, DateTime.MinValue);
}
}
else if (propertyInfo.PropertyType.IsEnum)
{
string enumName = Enum.GetName(propertyInfo.PropertyType, reader[props[0].FieldName]);
propertyInfo.SetValue(instance, Enum.Parse(propertyInfo.PropertyType, enumName));
}
else if (propertyInfo.PropertyType == typeof(Image))
{
if (reader[props[0].FieldName].GetType() != typeof(System.DBNull))
{
propertyInfo.SetValue(instance, Image.FromStream(reader.GetStream(reader.GetOrdinal(props[0].FieldName))));
}
}
else if (propertyInfo.PropertyType.BaseType == typeof(Stream))
{
// TODO: File PROPERTIES
//if (reader[props[0].FieldName].GetType() != typeof(System.DBNull))
// propertyInfo.SetValue(instance, reader.GetStream(reader.GetOrdinal(props[0].FieldName)));
}
else if (this.IsOrmInstance(propertyInfo.PropertyType))
{
propertyInfo.SetValue(instance, this.GetORMInstance(propertyInfo.PropertyType,
(long)reader[props[0].FieldName]));
}
else
{
throw new Exception("Data mapping error: Not supported data type conversion for type " + propertyInfo.PropertyType.FullName);
}
}
}
public List <Article> GetItems(SQLiteConnection con, long offset, long limit, Dictionary <string, object> filters)
{
List <Article> articles = new List <Article>((int)limit);
using (SQLiteCommand cmd = con.CreateCommand())
{
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT
ITM.ITMREF,
ITM.ITMDES1,
ITM.ITMSTA,
ITM.TSICOD0,
ITM.TSICOD1,
ITM.TSICOD2,
ITM.TSICOD3,
ITM.TSICOD4,
ITM.TCLCOD,
ITM.STU,
ITM.LASCUNDAT,
ITM.PHYSTO,
ITM.MFMQTY,
ITM.FLGMVT,
ITM.FLGCUN,
ITM.FLGMFM,
ITM.FLGSTO,
count(CNT.ITMREF) as LABELS,
sum(CNT.VALUE) as COUNTED,
ITM.CUNFLG,
ITM.CUNLISNUM
FROM ITMMASTER ITM LEFT OUTER JOIN CNT ON ITM.ITMREF = CNT.ITMREF AND CNT.DELETED = 0
");
string prefix = "WHERE ";
if (filters != null && filters.Count > 0)
{
if (filters.ContainsKey("counting") && (bool)filters["counting"])
{
sql.Append(prefix + " ITM.CUNFLG = 1 ");
prefix = " AND ";
}
if (filters.ContainsKey("itmref"))
{
sql.Append(prefix + " ITM.ITMREF LIKE @litmref ");
prefix = " AND ";
cmd.Parameters.AddWithValue("@litmref", Like(filters["itmref"].ToString()));
}
if (filters.ContainsKey("itmdes"))
{
sql.Append(prefix + " ITM.ITMDES1 LIKE @litmdes ");
prefix = " AND ";
cmd.Parameters.AddWithValue("@litmdes", Like(filters["itmdes"].ToString()));
}
if (filters.ContainsKey("active"))
{
if ((bool)filters["active"])
{
sql.Append(prefix + " ITM.ITMSTA = 1 ");
}
else
{
sql.Append(prefix + " ITM.ITMSTA <> 1 ");
}
prefix = " AND ";
}
if (filters.ContainsKey("nocount"))
{
if ((bool)filters["nocount"])
{
sql.Append(prefix + " CNT.ITMREF IS NULL ");
prefix = " AND ";
}
}
if (filters.ContainsKey("tsicod3"))
{
object v = filters["tsicod3"];
string[] cods = v as string[];
if (cods != null)
{
sql.Append(prefix + " ITM.TSICOD2 >= @ltsicod3s AND ITM.TSICOD2 <= @ltsicod3e ");
prefix = " AND ";
cmd.Parameters.AddWithValue("@ltsicod3s", cods[0]);
cmd.Parameters.AddWithValue("@ltsicod3e", cods[1]);
}
else
{
sql.Append(prefix + " ITM.TSICOD2 = @ltsicod3 ");
prefix = " AND ";
cmd.Parameters.AddWithValue("@ltsicod3", v.ToString());
}
}
if (filters.ContainsKey("tclcod"))
{
sql.Append(prefix + " ITM.TCLCOD LIKE @ltclcod ");
prefix = " AND ";
cmd.Parameters.AddWithValue("@ltclcod", filters["tclcod"].ToString());
}
}
sql.Append("GROUP BY ITM.ITMREF LIMIT @limit OFFSET @offset");
cmd.CommandText = sql.ToString();
cmd.Parameters.AddWithValue("@limit", limit);
cmd.Parameters.AddWithValue("@offset", offset);
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
Article res = new Article();
res.Itmref = rd.GetString(0);
res.Itmdes = rd.GetString(1);
res.Itmsta = (int)rd.GetByte(2);
res.Tsicod0 = rd.GetString(3);
res.Tsicod1 = rd.GetString(4);
res.Tsicod2 = rd.GetString(5);
res.Tsicod3 = rd.GetString(6);
res.Tsicod4 = rd.GetString(7);
res.Tclcod = rd.GetString(8);
res.Stu = rd.GetString(9);
res.Lascundat = rd.IsDBNull(10) ? DateTime.MinValue : rd.GetDateTime(10);
res.Physto = rd.IsDBNull(11) ? -1 : (double)rd.GetDecimal(11);
res.Mfmqty = rd.IsDBNull(12) ? -1 : (double)rd.GetDecimal(12);
res.Mvt = rd.GetByte(13) != 0;
res.Cun = rd.GetByte(14) != 0;
res.Mfm = rd.GetByte(15) != 0;
res.Sto = rd.GetByte(16) != 0;
res.LabelCount = rd.IsDBNull(17) ? 0 : rd.GetInt32(17);
res.CountedQuantity = rd.IsDBNull(18) ? 0 : (double)rd.GetDecimal(18);
res.Cunflg = rd.GetByte(19) != 0;
res.Cunlisnum = rd.IsDBNull(20) ? string.Empty : rd.GetString(20);
articles.Add(res);
}
}
}
return(articles);
}
/// <summary>
/// Converts attributes from an <see cref="SQLiteDataReader"/> to an Entity
/// </summary>
/// <param name="table">The <see cref="TableMapping"/> for this Entity</param>
/// <param name="reader">The current, open DataReader object</param>
/// <returns></returns>
internal TEntity ConvertToEntity <TEntity>(TableMapping table, SQLiteDataReader reader)
{
// Use reflection to map the column name to the object Property
TEntity entity = (TEntity)Activator.CreateInstance(table.EntityType, new object[] { });
for (int i = 0; i < reader.FieldCount; ++i)
{
string attrName = reader.GetName(i);
PropertyInfo property = table.GetAttribute(attrName).Property;
if (property.PropertyType.IsEnum)
{
var value = Enum.Parse(property.PropertyType, reader.GetValue(i).ToString());
property.SetValue(entity, value);
}
else
{
// SQLite doesn't support nearly as many primitive types as
// C# does, so we must translate
switch (Type.GetTypeCode(property.PropertyType))
{
case TypeCode.Byte:
property.SetValue(entity, reader.GetByte(i));
break;
case TypeCode.Int16:
property.SetValue(entity, reader.GetInt16(i));
break;
case TypeCode.Int32:
property.SetValue(entity, reader.GetInt32(i));
break;
case TypeCode.Int64:
property.SetValue(entity, reader.GetInt64(i));
break;
case TypeCode.Boolean:
property.SetValue(entity, reader.GetBoolean(i));
break;
case TypeCode.Decimal:
property.SetValue(entity, reader.GetDecimal(i));
break;
case TypeCode.Double:
property.SetValue(entity, reader.GetDouble(i));
break;
case TypeCode.Char:
property.SetValue(entity, reader.GetChar(i));
break;
case TypeCode.DateTime:
if (!reader.IsDBNull(i))
{
property.SetValue(entity, reader.GetDateTime(i));
}
break;
default:
// Correct DBNull values
object val = reader.GetValue(i);
if (val is DBNull)
{
continue;
}
property.SetValue(entity, val);
break;
}
}
}
// Foreign keys!
table.CreateRelationships(entity, this);
// Add object
return(entity);
}
/// <summary>
/// 将SqlDataReader转成T类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
public static T To <T>(this SQLiteDataReader reader) where T : new()
{
if (reader == null || reader.HasRows == false)
{
return(default(T));
}
var res = new T();
var propInfos = GetFieldnameFromCache <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
var n = reader.GetName(i).ToLower();
if (propInfos.ContainsKey(n))
{
PropertyInfo prop = propInfos[n];
object defaultValue = null;//引用类型或可空值类型的默认值
if (prop.PropertyType == typeof(string))
{
defaultValue = default(string);
var v = reader.GetString(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(int))
{
defaultValue = default(int);
var v = reader.GetInt32(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(decimal))
{
defaultValue = default(decimal);
var v = reader.GetDecimal(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(double))
{
defaultValue = default(double);
var v = reader.GetDouble(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(DateTime))
{
defaultValue = default(DateTime);
var v = reader.GetString(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else if (prop.PropertyType == typeof(bool))
{
defaultValue = default(bool);
var v = reader.GetBoolean(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
else
{
defaultValue = default(object);
var v = reader.GetValue(i);
prop.SetValue(res, (Convert.IsDBNull(v) ? defaultValue : v), null);
}
}
}
return(res);
}
public bool FillOrders(repositories.Order orderRepository)
{
DateTime dt1 = DateTime.Now;
SQLiteCommand command = new SQLiteCommand("SELECT * FROM order_list WHERE cancel_at IS NULL ORDER BY id DESC LIMIT 1000", db.connection);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
entities.Order order = BuildOrder(reader);
orderRepository.Add(order);
}
}
reader.Close();
foreach (entities.Order order in orderRepository.GetList())
{
SQLiteCommand elementCommand = new SQLiteCommand("SELECT * FROM order_element_list WHERE order_id = " + order.GetId().ToString(), db.connection);
SQLiteDataReader elementReader = elementCommand.ExecuteReader();
if (elementReader.HasRows)
{
while (elementReader.Read())
{
string cancelAt;
if (!elementReader.IsDBNull(6))
{
cancelAt = elementReader.GetString(6);
}
else
{
cancelAt = "";
}
entities.OrderElement element = new entities.OrderElement(elementReader.GetInt32(0), elementReader.GetInt32(1), elementReader.GetString(3), Convert.ToDouble(elementReader.GetDecimal(5)), elementReader.GetInt32(4), cancelAt);
order.AddElement(element);
}
}
elementReader.Close();
}
return(true);
}
//method for when the selected class changes
private void cbEditClass_SelectedIndexChanged(object sender, EventArgs e)
{
//updates buttons and links (will most likely enable)
updateClassButtons();
//get current class id
currentClassId = classId[cbEditClass.SelectedIndex];
//database supports multiple professors but this application only supports a single professor per class
SQLiteDataReader classProf = Database.executeQuery("SELECT ProfID FROM ClassProfessor WHERE ClassID = '" + currentClassId + "';");
//read single result
if (classProf.Read() == true)
{
int classProfId = classProf.GetInt32(0);
cbClassProfessor.SelectedIndex = profId.IndexOf(classProfId);
classHasProf = true;
}
else
{
classHasProf = false;
cbClassProfessor.SelectedIndex = -1;
}
classProf.Close();
//dynamically populate class information
SQLiteDataReader selectedClass = Database.executeQuery("SELECT * FROM Class WHERE ClassID = '" + currentClassId + "';");
//only one item should be returned
if (selectedClass.Read() == true)
{
//populate form with information from database
txtClassName.Text = selectedClass.GetString(1);
ctrCredits.Value = selectedClass.GetDecimal(2);
chkClassMonday.Checked = Convert.ToBoolean(selectedClass.GetString(3));
chkClassTuesday.Checked = Convert.ToBoolean(selectedClass.GetString(4));
chkClassWednesday.Checked = Convert.ToBoolean(selectedClass.GetString(5));
chkClassThursday.Checked = Convert.ToBoolean(selectedClass.GetString(6));
chkClassFriday.Checked = Convert.ToBoolean(selectedClass.GetString(7));
//load semester if not null
if (selectedClass.IsDBNull(8) == false)
{
cbSemester.SelectedIndex = semesterId.IndexOf(selectedClass.GetInt32(8));
}
else
{
cbSemester.SelectedIndex = -1;
}
dtClassStartTime.Value = selectedClass.GetDateTime(9);
dtClassEndTime.Value = selectedClass.GetDateTime(10);
//class location
txtClassLocation.Text = selectedClass.GetValue(11).ToString();
//dynamically add letter grades to drop down list
SQLiteDataReader letterGrades = Database.executeQuery("SELECT GradeLetter FROM GradingScaleValue WHERE ClassID ='" + currentClassId + "' ORDER BY BottomPercentage DESC;");
cbFinalLetterGrade.Items.Clear();
while (letterGrades.Read() == true)
{
cbFinalLetterGrade.Items.Add(letterGrades.GetString(0));
}
letterGrades.Close();
//determine whether the final letter grade is null (if it is, assume the class
// is not finished)
if (selectedClass.IsDBNull(14) == false)
{
chkClassFinished.Checked = true;
cbFinalLetterGrade.Text = selectedClass.GetString(14);
}
else
{
chkClassFinished.Checked = false;
cbFinalLetterGrade.SelectedIndex = -1;
}
}
selectedClass.Close();
}
/// <summary>
///
/// </summary>
/// <param name="column"></param>
/// <param name="valueIfNull"></param>
/// <returns></returns>
public decimal GetDecimal(int column, decimal valueIfNull)
{
return(IsDBNull(column) ? valueIfNull : currentReader.GetDecimal(column));
}
public List <FundStatus> GetFundStatus()
{
List <FundStatus> result = new List <FundStatus>();
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(connectionStr);
conn.Open();
string sql = string.Format("SELECT * FROM FundStatus");
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FundStatus model = new FundStatus();
model.Id = Guid.Parse(reader.GetString(0));
model.Date = reader.GetDateTime(1);
model.YesterdayBalance = reader.GetDecimal(2);
model.CustomerRights = reader.GetDecimal(3);
model.Remittance = reader.GetDecimal(4);
model.MatterDeposit = reader.GetDecimal(5);
model.ClosedProfit = reader.GetDecimal(6);
model.Margin = reader.GetDecimal(7);
model.Commission = reader.GetDecimal(8);
model.FreeMargin = reader.GetDecimal(9);
model.TodayBalance = reader.GetDecimal(10);
model.VentureFactor = reader.GetDouble(11);
model.FloatingProfit = reader.GetDecimal(12);
model.AdditionalMargin = reader.GetDecimal(13);
model.SettlementType = (SettlementType)reader.GetInt32(14);
model.AccountId = Guid.Parse(reader.GetString(15));
result.Add(model);
}
conn.Close();
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(result);
}
// Token: 0x0600006A RID: 106 RVA: 0x000033B8 File Offset: 0x000015B8
public static T To <T>(this SQLiteDataReader reader) where T : new()
{
if (reader == null || !reader.HasRows)
{
return(default(T));
}
T t = Activator.CreateInstance <T>();
Dictionary <string, PropertyInfo> fieldnameFromCache = SqlDataReaderEx.GetFieldnameFromCache <T>();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i).ToLower();
if (fieldnameFromCache.ContainsKey(key))
{
PropertyInfo propertyInfo = fieldnameFromCache[key];
if (propertyInfo.PropertyType == typeof(string))
{
object obj = null;
string @string = reader.GetString(i);
propertyInfo.SetValue(t, Convert.IsDBNull(@string) ? obj : @string, null);
}
else if (propertyInfo.PropertyType == typeof(int))
{
object obj = 0;
int @int = reader.GetInt32(i);
propertyInfo.SetValue(t, Convert.IsDBNull(@int) ? obj : @int, null);
}
else if (propertyInfo.PropertyType == typeof(decimal))
{
object obj = 0m;
decimal @decimal = reader.GetDecimal(i);
propertyInfo.SetValue(t, Convert.IsDBNull(@decimal) ? obj : @decimal, null);
}
else if (propertyInfo.PropertyType == typeof(double))
{
object obj = 0.0;
double @double = reader.GetDouble(i);
propertyInfo.SetValue(t, Convert.IsDBNull(@double) ? obj : @double, null);
}
else if (propertyInfo.PropertyType == typeof(DateTime))
{
object obj = default(DateTime);
string string2 = reader.GetString(i);
propertyInfo.SetValue(t, Convert.IsDBNull(string2) ? obj : string2, null);
}
else if (propertyInfo.PropertyType == typeof(bool))
{
object obj = false;
bool boolean = reader.GetBoolean(i);
propertyInfo.SetValue(t, Convert.IsDBNull(boolean) ? obj : boolean, null);
}
else
{
object obj = null;
object value = reader.GetValue(i);
propertyInfo.SetValue(t, Convert.IsDBNull(value) ? obj : value, null);
}
}
}
return(t);
}
public static decimal getDecimal(int numField)
{
return(m_objSQLiteDataReader.GetDecimal(numField));
}
public decimal ReadDecimal(SQLiteDataReader reader, int columnNumber)
{
return(reader.IsDBNull(columnNumber) ? 0m : reader.GetDecimal(columnNumber));
}
private void reportGenerateBtn_Click(object sender, EventArgs e)
{
//chart2.Series["Series1"].Points.Clear();
//chart2.Series["Series2"].Points.Clear();
sql_con.Open();
if (this.radioButtonVal == 1)
{
chart2.Visible = true;
//chart1.Visible = true;
String sql = "select count(EmployeeID)as Ecount, Faculty" +
" from Lecturer" +
" group by Faculty";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Staff Count Per Faculty");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series1"].Points.AddXY(reader.GetString(1), reader.GetInt32(0));
}
}
if (radioButtonVal == 2)
{
chart2.Visible = true;
String sql = "select count(EmployeeID)as Employeecount, Center" +
" from Lecturer" +
" group by Center";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Total Staff Per Center");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series1"].Points.AddXY(reader.GetString(1), reader.GetInt32(0));
}
}
if (radioButtonVal == 3)
{
chart2.Visible = true;
String sql = "Select YearSem, count(Groups) as GroupsCount " +
" from Student" +
" group by YearSem";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Student Groups Per Semester");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
//String year =
//String onlyYear = year.Remove(2);
chart2.Series["Series1"].Points.AddXY(reader.GetString(0), reader.GetInt32(1));
}
}
if (radioButtonVal == 4)
{
chart2.Visible = true;
String sql = "Select Programme, count(Groups) as SubGroupsCount " +
" from Student" +
" group by Programme";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Student Groups Per Programme");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
//String year =
//String onlyYear = year.Remove(2);
chart2.Series["Series1"].Points.AddXY(reader.GetString(0), reader.GetInt32(1));
}
}
if (radioButtonVal == 5)
{
chart2.Visible = true;
String sql = "select count(Subject)as Subcount, Year" +
" from Subject" +
" group by Year";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Number of Subjects Per Year");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series1"].Points.AddXY(reader.GetString(1) + "st Year", reader.GetInt32(0));
}
}
if (radioButtonVal == 6)
{
chart2.Visible = true;
String sql = "select Year,(avg(Evaluation))*0.01 as Hours" +
" from Subject" +
" group by Year";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
//chart2.Series["Series1"].LabelFormat ="#.##";
Title title = chart2.Titles.Add("Average Evaluation Time Per Year");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series2"].Points.AddXY(reader.GetString(0) + "st Year", reader.GetDecimal(1));
}
}
if (radioButtonVal == 7)
{
chart2.Visible = true;
//chart1.Visible = true;
String sql = "select Role,count(EmployeeID)as Ecount" +
" from Lecturer" +
" group by Role";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Staff Acording To Role ");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series1"].Points.AddXY(reader.GetString(0), reader.GetInt32(1));
}
}
if (this.radioButtonVal == 8)
{
chart2.Visible = true;
//chart1.Visible = true;
String sql = "select count(EmployeeID)as Ecount, Dept" +
" from Lecturer" +
" group by Dept";
SQLiteCommand command = new SQLiteCommand(sql, sql_con);
SQLiteDataReader reader = command.ExecuteReader();
Title title = chart2.Titles.Add("Staff Count Per Department");
title.Font = new System.Drawing.Font("Arial", 18, FontStyle.Bold);
title.ForeColor = System.Drawing.Color.FromArgb(0, 0, 205);
while (reader.Read())
{
chart2.Series["Series1"].Points.AddXY(reader.GetString(1), reader.GetInt32(0));
}
}
if (radioButtonVal == 0)
{
MessageBox.Show("Please select an Option ", "Select", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.radioButtonVal = 0;
sql_con.Close();
}